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

Transact-SQL