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

Question

In SQL Server 2000, how do you drop a stored procedure (SP) after making sure it already exists?

Answer 1

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

Conflicts over ownership are solved using object id(). You may see numerous procedures with the same name that are all for different owners if you do SELECT name FROM sysobjects WHERE name = "my procedure."

However, if more than one procedure with that name exists, SELECT * FROM sysobjects WHERE id = object id(N'[my procedure]') will only display the one for the current owner or user.

Still, identify the object owner at all times (default is dbo). This is not only free of unpleasant side effects, but it is also a little quicker.