Checking for the existence of a procedure

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 2000 Forums

Transact-SQL (2000)

Checking for the existence of a procedure

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-13 : 19:23:33

I'm checking to see if a procedure already exists and have 2 ways to go about this (someone else wrote the first one, I did the second one). I'm just wondering if my way is more efficient (if not, please direct me in the correct direction).

1st way:
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.spAddNewStudent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.spAddNewStudent
GO

I'm wondering if I can change a few things without screwing other things over (also wondering a few things). Changed:
--change * to id
--don't qualify stored procedure name with user 'dbo' (how bad would that be??)

2nd way:
if exists (select id from dbo.sysobjects where id = object_id(N'spAddNewStudent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure spAddNewStudent
GO
--I know most of everything that's going on, but just not sure why they selected every attribute instead of just the one they wanted to use.
--Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-13 : 19:34:38

It is best to use SELECT * in this instance. IF EXISTS/IF NOT EXISTS are the only times that you should use *. It is a best practice to preface the object with the owner.

I would recommend doing this instead though:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spAddNewStudent')
...

The INFORMATION_SCHEMA views should be used wherever possible instead of the system tables.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-13 : 20:15:51

I usually do it like this, because it's shorter to code.


if objectproperty(object_id(N'dbo.spAddNewStudent'),N'IsProcedure') = 1
	drop procedure dbo.spAddNewStudent

It works fine for tables and other objects also. You just have to pick the correct object property.

For a table:


if objectproperty(object_id(N'dbo.MyTable'),N'IsUserTable') = 1
	drop table dbo.MyTable

For a scalar function:


if objectproperty(object_id('dbo.F_MY_FUNCTION'),'IsScalarFunction') = 1
	begin drop function dbo.F_MY_FUNCTION end

CODO ERGO SUM

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-13 : 20:30:25

I do like your way tkizer, but I'm just wondering why its a 'best practice' to use '*' (if you know or can point me to some reading that'd be great, I've been searching google/ask/yahoo and have come up short)
--Nick
Thanks btw.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-13 : 20:46:52

If you want to use INFORMATION_SCHEMA.ROUTINES to check for the existence of a stored procedure, you should really fully qualify the query with the ROUTINE_TYPE and ROUTINE_SCHEMA, because it will also have functions, and could have the same procedure name with different owners.

It gets kind of long to code, and that's why I do it the way I posted before.


if exists (
select * 
from INFORMATION_SCHEMA.ROUTINES 
where
	ROUTINE_TYPE = 'PROCEDURE' and
	ROUTINE_SCHEMA = 'dbo' and
	ROUTINE_NAME = 'MyTable')
	begin drop table dbo.MyTable end

CODO ERGO SUM

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-13 : 20:57:24

Yes, your way does appear to be much shorter and more simple to understand.
--Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-14 : 12:57:21

If you've got strict naming standards for your objects, then you shouldn't need to fully qualify the query. All of our objects are owned by dbo. No duplicates names exist due to the naming standards.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources