Altering Stored Procedures

for RuBoard

Just as you create stored procedures using the CREATE PROCEDURE command, you alter them with ALTER PROCEDURE. The advantage of using ALTER PROCEDURE to change a stored procedure is that it preserves access permissions, whereas CREATE PROCEDURE doesn't. A key difference between them is that ALTER PROCEDURE requires the use of the same encryption and recompile options as the original CREATE PROCEDURE statement. If you omit or change them when you execute ALTER PROCEDURE, they'll be omitted or changed permanently in the actual procedure definition.

A procedure can contain any valid Transact-SQL command except these: CREATE DEFAULT, CREATE FUNCTION, CREATE PROC, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, SET SHOWPLAN_TEXT, and SET SHOWPLAN_ALL. These commands must reside in their own command batches, and, therefore, can't be part of a stored procedure. Procedures can create databases, tables, and indexes, but not other procedures, defaults, functions, rules, schemas, triggers, or views.

TIP

You can work around this limitationthe inability to construct most other kinds of objects from within a stored procedureby constructing a T-SQL string and executing it via sp_executesql or the EXEC () function, as shown in Listing 1-10:

Listing 1-10 You can create procedures, views, UDFs, and other objects from within stored procedures by using sp_executesql and EXEC().

 CREATE PROC test AS DECLARE @sql nvarchar(100) SET @sql=N'create proc dbo.test2 as select ''1''' EXEC dbo.sp_executesql @sql EXEC dbo.test2 GO EXEC dbo.test 

(Results)

 Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.test2'. The stored procedure will still be created. ---- 1 

The warning message is due to the fact that the test2 procedure doesn't exist when the test procedure is first created. You can safely ignore it.

for RuBoard