|
Subject:
|
Check if stored procedure exists.
|
|
Posted By:
|
phil
|
Post Date:
|
2/4/2004 9:06:38 PM
|
A basic question, but here's a snippet of a script I've downloaded:
IF EXISTS (select * from syscomments where id = object_id ('dbo.SPROC_NAME'))
BEGIN
DROP PROCEDURE dbo.SPROC_NAME
IF OBJECT_ID('dbo.SPROC_NAME') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.SPROC_NAME >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.SPROC_NAME >>>'
END
Now clearly it's determining if the stored procedure exists, dropping it if it does, and checking that it's been dropped.
My question is why use the "If Exists" test initially to check if the stored procedure exists, but then the OBJECT_ID() function to check that it's been dropped if it did exist?
|
|
Reply By:
|
sal
|
Reply Date:
|
2/4/2004 9:47:00 PM
|
Maybe they are making sure that you have the proper permissions to drop the database. Just because a user can execute the code does not mean that they have the rights to drop objects.
That is the only explanation I can come up with.
Sal
|
|
Reply By:
|
phil
|
Reply Date:
|
2/4/2004 11:54:16 PM
|
Thanks sal, I appreciate your comment and understand that may be the case. My problem wasn't with why the second test was performed, rather why the two tests are different.
Perhaps I didn't phrase the original question well, so let me try again.
In the first test to see if the SPROC exists it uses the "If Exists" test.
If the SPROC is found and an attempt to delete it is made, the OBJECT_ID() function test is used to see if it still exists after the attempted deletion.
I was wondering why the two tests to see if the SPROC exists are different (especially as the OBJECT_ID function is actually used in the sub query of the "If Exists" test). Couldn't the check of OBJECT_ID('dbo.SPROC_NAME') returning NULL have been used for both tests, or am I missing something obvious here?
There are a number of scripts that use this same syntax in a few places, so it isn't a one off.
Putting it another way, are the results of
IF EXISTS (select * from syscomments where id = object_id ('dbo.SPROC_NAME'))
and
IF OBJECT_ID('dbo.SPROC_NAME') IS NOT NULL
likely to be different? And, if they aren't, which is to be preferred and why? Thanks.
|
|
Reply By:
|
defiant
|
Reply Date:
|
2/9/2004 9:37:19 AM
|
object_id(sproc_name) will return 1 even if the sproc doesn't exist but there is another object with the sproc_name name. so that's why the first test has to use 'if exists'. object_id is used inside begin - end, where execution shouldn't enter unless there was a sproc called 'dbo.SPROC_NAME'.
the object_id() test is simpler and shorter, and this is why i think it was used inside begin - end.
defiant.
|
