Stored Procedures  «Prev  Next»

Lesson 7Deleting Stored Procedures
ObjectiveDescribe how to delete a user-defined stored procedure.

Deleting Stored Procedures

Here's how to delete a user-defined stored procedure in SQL Server 2019:
Methods: You have two primary techniques ways to accomplish this:
  1. SQL Server Management Studio (SSMS): Graphically through the interface.
  2. Transact-SQL (T-SQL): Using the `DROP PROCEDURE` command.

Using SQL Server Management Studio (SSMS):
  1. Connect: In Object Explorer, connect to your SQL Server instance and expand the database containing the stored procedure.
  2. Locate the Procedure: Expand the "Programmability" folder and then the "Stored Procedures" folder. Find the stored procedure you want to delete.
  3. Delete: Right-click on the stored procedure and select "Delete".
  4. Confirm: In the confirmation dialog box, click "OK".

Using Transact-SQL (T-SQL):
  1. Open Query Editor: In SSMS, click on "New Query".
  2. Execute the `DROP PROCEDURE` Command: Type the following command, replacing `[Procedure_Name]` with the actual name of your stored procedure: 
    DROP PROCEDURE [Procedure_Name]; 
GO
  3. Execute: Click the Execute button (or press F5).
Important Notes:
  • Irreversible: Deleting a stored procedure is permanent. Ensure you have backups if needed.
  • Dependencies: Before deleting a stored procedure, check if any other objects (like views or other procedures) depend on it. If so, you will need to modify those dependent objects first. SSMS usually has a feature to show dependencies.


Two Methods to Delete SQL-Server Stored Procedures

  1. To delete your stored procedure with Enterprise Manager:
    1. Right-click the name of the stored procedure shown in Enterprise Manager.
    2. Choose the Delete menu option.
  2. Delete using Transact-SQL
    To delete a stored procedure using Transact-SQL, use the DROP PROCEDURE statement, as shown below:
    Delete using Transact-SQL
    1. "procedure_name is the name of the procedure you are going to delete. Multiple stored procedures can be deleted at the same time by specifying them in a row, separated by commas
    2. DROP PROCEDURE procedure_name [, ...n]

Delete Stored Procedure Example

To delete the usp_DeleteEmployee stored procedure, simply execute this Transact-SQL statement: 
DROP PROCEDURE usp_DeleteEmployee

Two related concepts of user-defined stored procedures:
  1. the system and
  2. extended stored procedures.

Explanation:
  • DROP PROCEDURE: This is the SQL command specifically designed for removing stored procedures from a database.
  • usp_DeleteEmployee: This is the name of the stored procedure you want to delete. It's important to use the exact stored procedure name.

Important Considerations
  • Case Sensitivity: Depending on your SQL Server configuration, the procedure name might be case-sensitive. Double-check the exact capitalization in your database.
  • Dependencies: If other stored procedures or database objects rely on `usp_DeleteEmployee`, you might encounter errors when trying to delete it. You may need to address these dependencies first.
How to Execute:
  1. Open a query window in SQL Server Management Studio (SSMS) or another SQL client.
  2. Paste the `DROP PROCEDURE usp_DeleteEmployee` statement.
  3. Execute the query.

SEMrush Software