Altering Stored Procedure

Changing Stored Procedure with ALTER (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.

NOTE:

If you perform a DROP and then use a CREATE, you have almost the same effect as using an ALTER PROC statement with one rather big difference: if you DROP and CREATE, you need to reestablish your permissions for who can and can’t use the sproc.

In addition, SQL Server loses track of the dependency information for any procedures, views, triggers, and functions that depended on the sproc before you dropped it.

Below SP modifies the definition of the stored procedure created in above SP.

The new stored procedure, in addition to the table's name, receives a column's name as a parameter.

Alter procedure #testproc

@tabname varchar (30),

@colname varchar (20)

As

Declare

@qry varchar (100)

Set @qry='select '+@colname+' from '+@tabname

Exec (@qry)

go

Exec #testproc #num, id

When you alter a stored procedure's definition (using the ALTER PROC statement):

SQL Server keeps permissions intact on the stored procedure. As a result, any permissions set on the stored procedure are kept after changing the stored procedure's code using ALTER PROC.

This doesn't affect any dependent objects (tables, triggers, or stored procedures). For example, if you alter a stored procedure's definition and it references a table, the table isn't affected.

This doesn't affect the property to run automatically when SQL Server starts, if this was previously set using the sp_procoption system stored procedure.

For example, if you alter the code of the stored procedure created in Listing 8.6 (insertsqlstatus, which was set to run automatically whenever SQL Server is started), SQL Server keeps this property intact.

The main thing to remember when you edit sprocs with T-SQL is that you are completely replacing the existing sproc.

However, notice that if you just need to change an option, you still must specify the entire code of the stored procedure. Similarly, if you just have to change the code and preserve the options, you also must specify the options.

In other words, if you either want to change the procedure's code without affecting permissions and properties, or want to change the options of the stored procedure (WITH ENCRYPTION or WITH RECOMPILE), you can Use the ALTER PROCEDURE statement.

For example, if you want to encrypt the above mentioned code, you would have to add the WITH ENCRYPTION option to the definition of the stored procedure.

The only differences between using 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 permission that have been established for the sproc. It keeps the same object ID within system objects and allows the dependencies to be kept.

For example, if procedure A calls procedure B and you drop and re-create procedure B, you no longer see the dependency between the two. If you use ALTER, it’s all still there.

ALTER PROC retains any dependency information on other objects that may call the sproc being

Altered.

Below code shows you how to acomplish this and also shows that the code is in fact encrypted after executing this script.

Using ALTER PROC to Modify the Options of a Stored Procedure:

USE Northwind

GO

ALTER PROC dbo.issuequery

@tablename NVARCHAR(256),

@columname NVARCHAR(256)

WITH ENCRYPTION

AS

DECLARE

@query NVARCHAR(1000)

SET @query = 'SELECT '+ @columname + 'FROM '+ @tablename

EXEC (@query)

GO

To demonstrate encrypted source code:

sp_helptext #testproc

GO

Thanks...