How to Delete or Change a Stored Procedure

Script below presents the syntax of the DROP PROC statement. You use this statement to delete one or more stored procedures from the database. As with the other statements you’ve learned that delete objects from the database, the deletion is permanent.

The scripts below present the syntax of the ALTER PROC statement. You use this statement to redefine an existing stored procedure. As you can see, the syntax is the same as the syntax of the CREATE PROC statement.

Like the ALTER VIEW statement, ALTER PROC completely replaces the previous definition for the stored procedure. Because of that, you’ll usually change the definition of a stored procedure by deleting the procedure and then recreating it. If you’ve assigned security permissions to restrict the users who can call the procedure, however, those permissions are lost when you delete the procedure. If you want to retain the permissions, then, you should use the ALTER PROC statement instead.

The examples in this section show how you might use the ALTER PROC and DROP PROC statements. The first example creates a stored procedure named spVendorState that selects vendors from the state specified by the @State parameter. Because the SELECT statement will fail if a state isn’t specified, this parameter is required. In the second example, however, an ALTER PROC statement is used to modify this procedure so the state is optional. The last example deletes this procedure.

If you delete a table or view used by a stored procedure, you should be sure to delete the stored procedure as well. If you don’t, the stored procedure can still be called by any user or program that has access to it. Then, an error will occur because the table or view has been deleted.

The syntax of the DROP PROC statement

 

The syntax of the ALTER PROC statement

 

A statement that creates a procedure

 

A statement that changes the parameter defined by the procedure

 

A statement that deletes the procedure

 

Description

  • To delete a stored procedure from the database, use the DROP PROC statement.
  • To modify the definition of a procedure, you can delete the procedure and then create it again, or you can use the ALTER PROC statement to specify the new definition.
  • When you delete a procedure, any security permissions that are assigned to the procedure are also deleted. If that’s not what you want, you can use the ALTER PROC statement to modify the procedure and preserve the permissions.