Describe how to modify a user-defined stored procedure.
Modifying Stored Procedures | SQL-Server
If your business rules change, you might need to modify your stored procedures, using either Enterprise Manager or Transact-SQL.
Using Enterprise Manager
To modify your stored procedure with Enterprise Manager:
Right-click the name of the stored procedure shown in Enterprise Manager.
Choose the Properties menu option.
Enter the appropriate Transact-SQL statements in the Stored Procedure Properties dialog box, shown below:
CREATE PROCEDURE usp_DeleteEmployee @EmployeeID int AS
IF EXISTS (SELECT * FROM employees WHERE EmployeeID = @EmployeeID)
DELETE
FROM Employees
WHERE EmployeeID = @EmployeeID
ELSE
Altering a stored procedure
Using Transact-SQL
Use the ALTER PROCEDURE statement to modify a stored procedure. The syntax for this statement is shown in the following SlideShow:
As you probably noticed, the ALTER PROCEDURE syntax is almost the same as the CREATE PROCEDURE syntax.
Therefore, an alternative to modifying your stored procedures is to delete and re-create them. However, if you do so, you will lose all permissions assigned to the stored procedure and any reference to dependency objects.
Let us modify the stored procedure that we created in the prior lesson to delete data from the Timesheets table for the employee:
ALTER PROCEDURE usp_DeleteEmployee @EmployeeID int
AS
IF EXISTS (SELECT * FROM employees WHERE EmployeeID
= @EmployeeID)
BEGIN
DELETE
FROM Employees
WHERE EmployeeID = @EmployeeID
DELETE
FROM Timesheets
WHERE EmployeeID = @EmployeeID
END
ELSE
RAISERROR ('Employee ID does not exist', 16, 1)
In the next lesson, you will learn how to delete a stored procedure.