how to drop a store procedure if exists in sql server

 596 Total Views,  4 views today

You are no longer using a stored procedure and want to drop it from database.
Drop stored procedure from database using DROP PROCEDURE command.

Syntax:
use YourDatabaseName
go
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE object_id = OBJECT_ID('schema_name.procedure_name'))
BEGIN
    DROP PROCEDURE schema_name.procedure_name
END
GO
Example:
use banking_db
go
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE object_id = OBJECT_ID('schema_name.procedure_name'))
BEGIN
drop procedure dbo.wsp_Get_CustomerName
END 
go
Note:
Once a stored procedure is dropped its definition is removed from the database's system tables.
Code references to the stored procedure by other procedure or triggers will fail upon execution
once the stored procedure has been dropped.

Also See: how to drop a stored procedure in sql server

1 thought on “how to drop a store procedure if exists in sql server”

  1. SQL: solve these pleaseĀ 
    1. Customer (cid, name,sid), order(oid, cid, date, sid), salesman(sid,commsion)
    Count the salesman by their oid and date.
    2. Employee(ename, did, salary), Department (did, dname)
    Now update the salary 10% increase value from Department table.

Leave a Reply

Your email address will not be published. Required fields are marked *