Modifying and Renaming a Stored Procedure

If you need to change the statements or parameters in a stored procedure, you can either delete and re-create the stored procedure or alter the stored procedure in a single step. When you delete and re-create a stored procedure, all permissions associated with the stored procedure are lost. When you alter the stored procedure, the procedure or parameter definition is changed but the permissions defined for the stored procedure are retained.

You can also rename a stored procedure. The new name must follow the rules for identifiers. You can rename only the stored procedures that you own, but the database owner can change the name of any user’s stored procedure. The stored procedure to be renamed must be in the current database.

A stored procedure can also be modified to encrypt the definition or cause the procedure to be recompiled each time it is executed.

Note Changing the name or definition of a stored procedure can cause any dependent objects to fail when executed if those dependent objects are not also updated to reflect the changes made to the stored procedure.

To modify a stored procedure

To rename a stored procedure

Note Renaming a stored procedure does not change the name of the stored procedure in the text of the procedure’s definition. To change the name of the stored procedure in the definition, modify the stored procedure directly.

Caution Extended stored procedures should not be renamed unless the code used to define the extended stored procedure is changed too. Otherwise, the stored procedure will not be able to execute.

See Also

Deferred Name Resolution and Compilation