What Is the Syntax to Drop a Stored Procedure in Sql Server 2000?
Simple Question, as the Title Suggests: What Is the Syntax to Drop a Stored Procedure (Sp) in Sql Server 2000, by First Checking That the Sp Exists? Please...
Simple question, as the title suggests:
What is the syntax to drop a Stored Procedure (SP) in SQL Server 2000, by first checking that the SP exists?
Please provide the full code.
6 Answers
Microsoft recommended using the object_id() function, like so:
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[YourProcedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[YourProcedure]
GO
.
object_id() helps resolve owner conflicts. If you do
SELECT name FROM sysobjects WHERE name = 'my_procedure'
, you may see many different procedures with the same name -- all for different owners.
But, SELECT * FROM sysobjects WHERE id = object_id(N'[my_procedure]') will only show you the one for the current owner/user, if more than one procedure by that name exists.
Still, always specify the object owner (default is dbo). Not only does this avoid nasty side-effects, it's a little faster too.
A slightly simpler method without going to system tables:
IF OBJECT_ID('my_procedure') IS NOT NULL DROP PROCEDURE my_procedure
GO
Not for SQL Server 2000, but starting with SQL Server 2016, you can use the IF EXISTS syntax:
DROP PROCEDURE IF EXISTS [sp_ProcName]
Like this:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'my_procedure' AND type = 'P')
DROP PROCEDURE my_procedure GO
Hope that helps!
You can do the following if you want to remove multiple Procedures. NB: This syntax works on SQL Server 2016 and later
USE [Database_name]
GO
BEGIN
DROP PROCEDURE IF EXISTS 'my_procedure1',
'my_procedure2',
'my_procedure3',
'my_procedure4',
'my_procedure5',
END
In SQL SERVER 2008, if you want to drop a stored procedure just write the below command....
DROP PROC Procedure_name
DROP PROC dbo.spInsertUser
Hope it helps..
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
