Managing Stored Procedures

Stored procedure code and objects are the most complex to manage in SQL Server. On a large system, it does not take long to lose count of the number of stored procedures written and deployed on the system. The following sections explain how to alter and drop stored procedures using either T-SQL or Visual Studio or Management Studio.

Altering Stored Procedures

To alter a stored procedure in T-SQL, you need to use the ALTER PROCEDURE statement. The basic statement is as follows:

 ALTER PROCEDURE proc_name ON . . .

The code to apply after the ON line takes the same syntax and choice of arguments as the CREATE PROC statement described earlier (see the SQL Server 2005 Books Online for the full explanation and usage of the arguments).

To alter a stored procedure in Management Studio, follow the steps described earlier for creating a stored procedure in Management Studio and edit the stored procedure accordingly Altering a stored procedure in Management Studio is demonstrated shortly.

Dropping Stored Procedures

Dropping a stored procedure in T-SQL requires the DROP PROCEDURE statement followed by the procedure name. For example, the code

 USE Stores IF EXISTS (SELECT name FROM sysobjects    WHERE name = 'DebitStores') DROP PROCEDURE DebitStores

drops the stored procedure DebitStores from the Stores database.

You should make sure to check for stored procedure dependencies with the sp_depends stored procedure before dropping the stored procedure.

To drop a stored procedure interactively in Management Studio, simply drill down to the database and select the Stored Procedures node from the console tree. Select the stored procedure, right-click, and select Delete.

Getting Information about Stored Procedures

To obtain information from SQL Server about the stored procedures attached to the database, you should execute the system stored procedure sp_helptext. This system stored procedure returns the code for database objects like stored procedures, rules, and defaults. It queries the code in syscomments as mentioned earlier. Call sp_helptext as follows:

 sp_helptext [ @objname  = ] 'name' 

and the example would look like this:

 --to check the code of a stored procedure EXEC sp_helptext @objname = storedprocname 

This obviously will not work on encrypted stored procedures.