How to Delete a SQL Server stored procedure
In addition of creating stored procedures there is also the requirement of deleting stored procedures. This topic mentions how you are allowed to delete stored procedures that are no longer required.
The syntax to drop a stored procedure is pretty straight forward, here are few examples.
Case 1: Dropping Single Stored Procedure
For dropping a single stored procedure, use the DROP PROCEDURE or DROP PROC command as shown below.
DROP PROCEDURE dbo.uspGetAddress
DROP PROC dbo.uspGetAddress
Case 2: Dropping Multiple Stored Procedures
For dropping a multiple stored procedures with single command you mention each procedure separated by a comma as depicted below.
DROP PROCEDURE dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress
DROP PROC dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress
Editing and Modifying an existing SQL Server stored procedure
Once stored procedures are created, you may require modifying an existing stored procedure, when ever required. In this tutorial we are going to learn about the ALTER PROCEDURE command and how it is used.
- Editing or ALTERing a stored procedure is quite simple. Store procedures get stored within one of the system tables available in the databases, once a stored procedure has been created.
- Whenever you require modifying a stored procedure the entry which was originally made in the system table gets replaced by this new code.
- Also, SQL Server is going to recompile the stored procedure the next time it gets executed, hence users are using the new logic. The command to edit or modify an existing stored procedure is ALTER PROCEDURE or ALTER PROC.
Editing or Modifying an Existing Stored Procedure
Let’s take the following given below of the existing stored procedure: This permits us to do an exact match on the City.
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
WHERE City = @City
Let’s assume we want to change this to perform a LIKE instead of an equals.
To alter the stored procedure and save that updated code you must use the ALTER PROCEDURE command as shown below :
ALTER PROCEDURE dbo.uspGetAddress @City nvarchar(30)
WHERE City LIKE @City + ‘%’
Now next time, whenever that stored procedure is called by an end user it is going to use this new logic.