Check if a stored procedure exists

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

Check if a stored procedure exists

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 09:53:58

Hi All.

Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?

Thanks people.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 09:55:42

[code]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aaa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aaa]
GO

Create Proc aaa
...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 09:58:48

Thanks harsh :-)

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 10:12:30

OK ive used the following code:

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
@Reference int
AS
SELECT
Account.CanTravel,
Account.SEEmployee,
Account.WorkHours,
Account.DrivingLicence,
Account.CriminalConvictions,
Account.CriminalConvictionsDetails1,
Account.CriminalConvictionsDate1,
Account.CriminalConvictionsDetails2,
Account.CriminalConvictionsDate2,
Account.CriminalConvictionsDetails3,
Account.CriminalConvictionsDate3
FROM Account
WHERE Account.Reference = @Reference
END
GO

And i'm getting the follwoing errors. Can anyone tell me why?

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 19
Must declare the variable '@Reference'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:59:33

CREATE PROCEDURE needs to be the first statement in the batch! Read about CREATE PROCEDURE in Books Online.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	declare @sql varchar(8000)
	set @sql = '
	CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
	@Reference int
	AS
	SELECT
	Account.CanTravel,
	Account.SEEmployee,
	Account.WorkHours,
	Account.DrivingLicence,
	Account.CriminalConvictions,
	Account.CriminalConvictionsDetails1,
	Account.CriminalConvictionsDate1,
	Account.CriminalConvictionsDetails2,
	Account.CriminalConvictionsDate2,
	Account.CriminalConvictionsDetails3,
	Account.CriminalConvictionsDate3
	FROM Account
	WHERE Account.Reference = @Reference'

	exec (@sql)
END
GO

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 11:02:38

Why IF NOT EXISTS? Why not standard accepted syntax of IF EXISTS..DROP?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:06:12

[code]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoRead]
GO

CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
@Reference int
AS
SELECT
Account.CanTravel,
Account.SEEmployee,
Account.WorkHours,
Account.DrivingLicence,
Account.CriminalConvictions,
Account.CriminalConvictionsDetails1,
Account.CriminalConvictionsDate1,
Account.CriminalConvictionsDetails2,
Account.CriminalConvictionsDate2,
Account.CriminalConvictionsDetails3,
Account.CriminalConvictionsDate3
FROM Account
WHERE Account.Reference = @Reference
GO[/code]

Peter Larsson
Helsingborg, Sweden

rwilson513
Starting Member

1 Post

Posted - 2009-10-15 : 17:06:20

Keep in mind that the two approaches ARE DIFFERENT!!! ("if not exists" vs "if exists")... Consider this:

You are executing the script to create the stored proc "foo" (call it version A) on 10 different DB's. 5 of the DB's do not have a stored proc named "foo". The other 5 already have a stored proc named "foo", but the stored proc is different (call this version B).

Before the script:
5 - "foo" Version B
5 - NONE

After executing the "If NOT exists, then create" script:
5 - "foo" Version B
5 - "foo" Version A

Consider that, versus the after-effects of the "If exists, then drop, then create" script:
10 - "foo" Version A

So, it all depends on what you want to do. For example, if you don't want to change DB's that already have an existing stored proc, then use the "If NOT exists" method. If you want to wipe everything clean, and give every DB the same stored proc, then use the "If exists" method. Both are scenarios I've come across with.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-15 : 17:12:47

AND THE REASON...why M$ changed the way they scripted things

My guess is too many Bozo's where killing things and trying to blame M$

Hey, if you throw a gun into a kennel (??) of Monkeys, what do you think happens.

In any Case I prefer the Drop method...and have to go through pains to recreate it now

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources