Managing Stored Procedures by Using T-SQL

3 4

Now that you know how to create stored procedures, let's look at how to use TSQL commands to alter, drop, and view the contents of a stored procedure.

The ALTER PROCEDURE Statement

The ALTER PROCEDURE T-SQL statement is used to alter a stored procedure created by the use of CREATE PROCEDURE. When you use ALTER PROCEDURE, the original permissions set for the stored procedure are maintained, and any dependent stored procedures or triggers are not affected. (A dependent procedure or trigger is one that calls a procedure.)

The syntax for the ALTER PROCEDURE statement is similar to the syntax for CREATE PROCEDURE:

 ALTER PROC[EDURE] procedure_name                   [ {@parameter_name data_type} ] [= default] [OUTPUT]  [,...,n] AS t-sql_statement(s) 

In the ALTER PROCEDURE statement, you must rewrite the entire stored procedure, making the desired changes. For example, let's re-create the stored procedure GetUnitPrice, which we used in an earlier example, and alter the procedure to add a condition to check for unit prices greater than $100, as shown here:

 USE Northwind GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "GetUnitPrice" AND type = "P") DROP PROCEDURE GetUnitPrice GO CREATE PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUTPUT AS SELECT @unit_price = UnitPrice FROM Products WHERE ProductID = @prod_id GO ALTER PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUTPUT AS SELECT @unit_price = UnitPrice FROM Products WHERE ProductID = @prod_id AND UnitPrice > 100 GO 

Now let's grant execute permission on the stored procedure to the user DickB, with the following statement:

 GRANT EXECUTE ON GetUnitPrice TO DickB GO 

As stated previously, if we alter the stored procedure, the permission will be maintained. Let's alter the procedure to select rows whose UnitPrice column value is greater than 200, instead of 100, as follows:

 ALTER PROCEDURE GetUnitPrice @prod_id int, @unit_price money OUTPUT AS SELECT @unit_price = UnitPrice FROM Products WHERE ProductID = @prod_id AND UnitPrice > 200 GO 

The user DickB will still have execute permission on the stored procedure after this ALTER PROCEDURE statement is run.

The DROP PROCEDURE Statement

The T-SQL DROP PROCEDURE statement is simple—it deletes a stored procedure. You cannot restore a stored procedure after you drop it. If you want to use a deleted procedure, you must totally re-create it by using the CREATE PROCEDURE statement. All permissions on the dropped stored procedure will be lost and will have to be regranted. Here's an example that uses DROP PROCEDURE to drop the GetUnitPrice procedure:

 USE Northwind GO DROP PROCEDURE GetUnitPrice GO 

NOTE

---

To drop a stored procedure, you must be using the database in which it resides. Remember that to use a database, you execute the USE statement followed by the database name.

The sp_helptext Stored Procedure

The sp_helptext system stored procedure enables you to view the definition of a stored procedure and the statement that was used to create the procedure. (It can also be used to print the definition of a trigger, a view, a rule, or a default.) This capability is useful when you want to quickly recall the definition of a procedure (or one of the other objects just mentioned) while you're using ISQL, OSQL, or SQL Query Analyzer. You might also direct the output to a file to create a script of the definition that can be used to edit and re-create the procedure, as needed. To use sp_helptext, you must provide the name of your user-defined stored procedure (or other object name) as the parameter. For example, to view the statements used earlier to create the InsertRows procedure, use the following command. (Again, you must be using the database in which the procedure resides for this command to work.)

 USE MyDB GO sp_helptext InsertRows GO 

The output looks like this:

 Text ------------------------------------------------------------- CREATE PROCEDURE InsertRows @start_value int AS DECLARE @loop_counter int, @start_val int SET @start_val = @start_value _ 1 SET @loop_counter = 0 WHILE (@loop_counter < 5) BEGIN INSERT INTO mytable VALUES (@start_val + 1, 'new row') PRINT (@start_val) SET @start_val = @start_val + 1 SET @loop_counter = @loop_counter + 1 END