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.

Description

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
GO
— or
DROP PROC dbo.uspGetAddress
GO

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
GO
— or
DROP PROC dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress
GO

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.

Description

  • 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)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
 
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)
AS
SELECT *
FROM Person.Address
WHERE City LIKE @City + ‘%’
GO
 
Now next time, whenever that stored procedure is called by an end user it is going to use this new logic.

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.