16 October 2014

SQL Server: Delete/Drop Stored Procedure

Syntax:
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]

Example:

  1. Delete/drop a stored procedure named spGetData with dbo schema :
    DROP PROCEDURE dbo.spGetData
  2. Delete/drop several stored procedures, e.g. 3 stored procedures named spGetData1, spGetData2, and spGetData3 with dbo schema:
    DROP PROCEDURE dbo.spGetData1, dbo.spGetData2, dbo.spGetData3
Otherwise if we want to delete/drop by checking if exist first, use this query:
IF EXISTS (select * from dbo.sysobjects 
           where id = object_id(N'[dbo].[STOREDPROCEDURENAME]') 
           and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[STOREDPROCEDURENAME] GO

Click here if you like this article.


0 comments: