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:
1) procedure_name is the name of the stored procedure you are going to create
2) number specifies the procedure number, if you use multiple procedures with the same name. This is used very infrequently. The main advantage is that if you delete the stored procedure with the DROP PROCEDURE command, you do not need to specify the number because all stored procedures in the group are dropped at the same time.
3) @parameter is the name of the argument that is used to pass values into, and possibly out of, the stored procedure. Multiple parameters can be specified.
4) data_type is the datatype of the parameter. Every parameter must have a datatype.
5) VARYING specifies the parameter, and can be varied in its type. This can be specified only when using a cursor variable
6) default is a placeholder for a value that is to be assigned to the parameter as a default. A Default is a value that is used if no explicit value is supplied.
7) OUTPUT indicates that the stored procedure will pass this value back to a calling program.
8) WITH RECOMPILE indicates that SQL Server will recompile the query plan every time it is executed, as opposed to reusing it from cache. This option is seldom used because caching the query plan is one of the main advantages of using stored procedures. The advantage to this option is that it guarantees that the query plan that is used will not be outdated. You cannot use this option with the FOR REPLICATION option.
9) WITH ENCRYPTION encrypts the text of the stored procedure so that it cannot be read from the syscomments table.
10) WITH RECOMPILE, ENCRYPTION specifies both the WITH RECOMPILE and WITH ENCRYPTION options.
11) FOR REPLICATION indicates that this stored procedure will be used only by replication subscribers and not by other processes in the database
12) As sql_code specifies that Transact-SQL code will be used to define your stored procedure. Multiple Transact-SQL statements are often used in the definition of a stored procedure.
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.
