Drop stored procedure 'sp1' if exist"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Drop stored procedure 'sp1' if exist"

Author

Topic

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2007-06-19 : 07:52:34

How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 08:06:41

This is what we used to do in SQL 2000, not sure its still as valid for SQL 2005


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySproc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MySproc
END

Kristen

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2007-06-19 : 08:14:10

thanks a lot.....

can i do the same using syntax similar given below

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.........
?

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 08:22:25

I expect so, but I doubt that the ID numbers are exposed to allow you to us the functions above.

Try a Generate Script (using the SQL Tools), that should give you a suitable example.

Kristen

Carat
Yak Posting Veteran

92 Posts

Posted - 2007-07-03 : 04:53:29

We use the following statement at the beginning of our scripts:
----------------------------------------------------------
use databasename
GO
if object_id('schema.StoredProcedureName') is not null
drop procedure schema.StoredProcedureName
GO
----------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources