Check if stored procedure exists.

phil · February 4th, 2004, 10:06 PM

A basic question, but here's a snippet of a script I've downloaded:

Code:

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?

sal · February 4th, 2004, 10:47 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

phil · February 5th, 2004, 12:54 AM

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

Code:

IF EXISTS (select * from syscomments where id = object_id ('dbo.SPROC_NAME'))

and

Code:

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.

defiant · February 9th, 2004, 10:37 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.