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:
DROP PROCEDURE [dbo].[cgpdbo_TestTable]
And several variants of the above commands, but I keep getting errors.
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]
SQL Server Helper Forums
Is that the easy way .... ??
Michael Parker replied to F Cali on 08-Jan-07 09:29 AM
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
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
IF EXISTS dbo.cgpdbo_TestTable DROP dbo.cgpdbo_TestTable
--DROP PROCEDURE [dbo].[cgpdbo_TestTable]
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
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)
DROP PROCEDURE sp1
PRINT('No Procedure Found')
Hope this helps.