SQL Server - Syntax for Drop Existing Procedure when IF Exists

Asked By Michael Parker on 08-Jan-07 09:10 AM

I would like to do a conditional drop of a stored procedure, but have not gotten it to work. I have tried:


 IF(EXISTS([dbo].[cgpdbo_TestTable])) then
 DROP PROCEDURE [dbo].[cgpdbo_TestTable]
 END IF


And several variants of the above commands, but I keep getting errors.

Response

F Cali replied to Michael Parker on 08-Jan-07 09:25 AM

To drop a stored procedure if it exists, you can do it this way:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[YourSPHere]') AND
                 OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[YourSPHere]
GO

SQL Server Helper Forums
http://www.sql-server-helper.com/forums/default.asp

Is that the easy way .... ??

Michael Parker replied to F Cali on 08-Jan-07 09:29 AM
end of post

Response

F Cali replied to Michael Parker on 08-Jan-07 09:42 AM

That's the standard script generated by SQL Server when you try to use the Generate Script utility in Enterprise Manager.

SQL Server Helper
http://www.sql-server-helper.com/default.aspx

K Pravin Kumar Reddy replied to Michael Parker on 08-Jan-07 09:44 AM
You may want to add a check for the object in the beginning of the SQL to DROP the stored procedure if it exists (IF EXISTS dbName.dbo.dsp_My_Proc DROP dbName.dbo.dsp_MyProc,) before the CREATE PROCEDURE line would run.
Error: incorrect syntax.
Michael Parker replied to K Pravin Kumar Reddy on 08-Jan-07 09:58 AM

I like the one line approach best.

When I attempt to run the following, I get an error msg: "Incorrect syntax near 'dbo'. ", which is referring to the line: IF EXISTS dbo.cgpdbo_TestTable DROP dbo.cgpdbo_TestTable

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

IF EXISTS dbo.cgpdbo_TestTable DROP dbo.cgpdbo_TestTable

GO

--IF(EXISTS([dbo].[cgpdbo_TestTable])) then

--DROP PROCEDURE [dbo].[cgpdbo_TestTable]

--END IF

CREATE PROCEDURE [dbo].[cgpdbo_TestTable] (

@testID int,--(need size),Int32

Not a Valid Syntax
F Cali replied to Michael Parker on 08-Jan-07 11:00 AM

You IF EXISTS statement is not a valid SQL statement.  You have to perform a SELECT to determine if your sp exists in the dbo.sysobjects table.

SQL Server Helper - Frequently Asked Questions
http://www.sql-server-helper.com/faq/index.aspx

Re :: Syntax to Drop Existing Stored Procedure
Shailendrasinh Parmar replied to Michael Parker on 04-Mar-09 05:00 PM

See the following SQL code to remove the strored procedure

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('sp1') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN 
 DROP PROCEDURE sp1
END 
ELSE
BEGIN
 PRINT('No Procedure Found')
END

Hope this helps.