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

Question

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.

score 64 · Accepted Answer · answered Aug 03 '10 at 00:10

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.

I'm using MS SQL 2012, this syntax worked for me DROP PROCEDURE [dbo].[YourProcedure] — Pawan Pareek, Dec 30 '17 at 11:21

score 11 · Answer 2 · answered Dec 03 '19 at 22:33

11

Not for SQL Server 2000, but starting with SQL Server 2016, you can use the IF EXISTS syntax:

DROP PROCEDURE IF EXISTS [sp_ProcName]

answered Dec 03 '19 at 22:33

Metaphor

6,157
10
54
77

1

This should be the top answer; OP got his solution a decade ago, this is now the correct one. Most people reading this don't need the clunky SQL Server 2000 solution. – MGOwen Apr 03 '20 at 06:58
That's not true, the question specifically asks for 2000, there are plenty of questions answering for newer versions. – Lozenger Jul 06 '21 at 08:17

score 10 · Answer 3 · answered Sep 18 '13 at 13:31

10

A slightly simpler method without going to system tables:

IF OBJECT_ID('my_procedure') IS NOT NULL DROP PROCEDURE my_procedure
GO

answered Sep 18 '13 at 13:31

Metaphor

6,157
10
54
77

score 3 · Answer 4 · answered Aug 02 '10 at 10:52

3

Like this:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'my_procedure' AND type = 'P')
DROP PROCEDURE my_procedure GO

Hope that helps!

answered Aug 02 '10 at 10:52

codykrieger

1,750
15
19

score 0 · Answer 5 · answered Jun 24 '20 at 07:51

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

score -2 · Answer 6 · edited Mar 26 '16 at 04:56

-2

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

edited Mar 26 '16 at 04:56

Syakur Rahman

2,056
32
40

answered Mar 26 '16 at 04:01

Aamir Shaikh

71
4

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

6 Answers6