What is the syntax to drop a Stored Procedure in SQL Server 2000?

stored-procedures sql-server-2000 sql-drop
Saajid Ismail picture Saajid Ismail · Aug 2, 2010 · Viewed 80k times · Source

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.

Answer

Brock Adams picture Brock Adams · Aug 3, 2010

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.

Related questions

SQL Server 2000: How to exit a stored procedure?

How can I exit in the middle of a stored procedure? I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on running: …

Read More
How can I generate a temporary table filled with dates in SQL Server 2000?

I need to make a temporary table that holds of range of dates, as well as a couple of columns that hold placeholder values (0) for future use. The dates I need are the first day of each month between $startDate …

Read More
Check if record exists, if yes "update" if not "insert"

I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table. What am I doing wrong? It increases timeValid for +30 days but inserts …

Read More