if exists/drop procedure syntax

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

General SQL Server Forums

New to SQL Server Programming

if exists/drop procedure syntax

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-03 : 08:35:14

Why am i getting syntax (incorrect syntax near 'drop') on the following:
GO
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME='dbo.sp_getstats' DROP PROCEDURE [dbo.SP_Getstats]
Go

i need to check for the existence of this sproc in the master db before it runs it's create script.

thank you

Bangers and Mash are neither "Bangers" nor "Mash".

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-03 : 09:02:59

Disregard. Got the following to take:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[sp_getstats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_getstats]

Bangers and Mash are neither "Bangers" nor "Mash".

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-03 : 09:12:41

Hi,

Check below link for your future reference

http://msdn.microsoft.com/en-us/library/ms187926.aspx

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-03 : 09:21:15

Thank you for that. Having cleared that obstacle, i'm hitting a new one that hadn't happened previous to adding the next steps in the job.

Basically the agent job is doing the following step by step:
step 1 - Create the db 'sizing_db'. There is an if exists/drop statement at the beginning of this step:
USE [master]
GO

/****** Object: Database [SIZING_DB] Script Date: 12/02/2011 14:32:17 ******/

IF EXISTS (SELECT Name FROM Sysobjects WHERE Name = 'SIZING_DB')
DROP DATABASE [SIZING_DB]
Go

My agent job is now failing on step 1, saying "there is already a database named 'sizing_db', choose another database.

Why is the if exists/drop statement at the lead of this step not doing what i expect and clearing the way for the create script to run?

Bangers and Mash are neither "Bangers" nor "Mash".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 12:54:06

shouldnt it be the below as you're looking for database?


IF EXISTS (SELECT 1 FROM sys.databases WHERE Name = 'SIZING_DB')
DROP DATABASE [SIZING_DB]
Go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-03 : 12:57:19

Ahhh. Thank you once again.

Bangers and Mash are neither "Bangers" nor "Mash".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 12:59:11

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources