Professional SQL Server™ 2005 Programming

11.2. Changing Stored Procedures with ALTER

ALTER statements for sprocs work almost identically to views from the standpoint of what an ALTER statement does.

The main thing to remember when you edit sprocs with T-SQL is thatyou are completely replacing the existing sproc. The only differences betweenusing the ALTER PROC statement and the CREATE PROC statement are as follows:

• ALTER PROC expects to find an existing sproc, whereas CREATE doesn't.

• ALTER PROC retains any permissions that have been established for the sproc. It keeps the same object ID within system objects and allows the dependenciesto be kept. For example, if procedure A calls procedure B and you drop andre-create procedure B, you no longer see the dependency between the two. If youuse ALTER, it's all still there.

• ALTER PROC retains any dependency information on other objects that maycall the sprocbeing altered.

NOTE

If you perform a DROP and then use a CREATE, you have almost the sameeffect as using an ALTER PROC statement with onerather big difference: if you DROP and CREATE, you need to reestablish your permissions forwho can and can't use the sproc. In addition, SQL Server loses track of thedependencyinformation for any procedures, views, triggers, and functions thatdepended on the sproc before you dropped it.