Microsoft® SQL Server™ 2000 Programming by Example

Altering Stored Procedure Definitions

The code of a stored procedure can be modified using the ALTER PROCEDURE statement,or its equivalent ALTER PROC. In SQL Server 6.5 and earlier, the only way to change a stored procedure's definition was to drop and re-create it, but this approach has one drawback: Permissions and properties set on the stored procedure are lost. Therefore, after re-creating the stored procedure, the database administrator had to set permissions again.

Listing 8.12 modifies the definition of the stored procedure created in Listing 8.11. The new stored procedure, in addition to the table's name, receives a column's name as a parameter.

Code Listing 8.12. Using ALTER TABLE to Modify the Code of a Stored Procedure

USE Northwind ...