stored-proceduressql-server-2000sql-drop

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 provide the full code.

Solution

  • 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.