CODEWITHSUNDEEP
sql-serverstored-procedures
Deqing
Deqing

Reputation: 14632

Different ways to check if a stored procedure exists in SQL Server

I found two ways to check if a stored procedure exists in our current code:

if object_id('dbo.procedure_to_drop') is not null

vs

if exists (select * from sysobjects where id = object_id('dbo.procedure_to_drop') and sysstat & 0xf = 4)

What's the difference between them and which one is the prefer way?

Upvotes: 3

Views: 13773

Answers (1)

Morzel
Morzel

Reputation: 305

The first version checks if any object exists with the given name. In that case, if you make a typo and enter a name of a table, it will still pass. About the second version is obsoleted because there is no guarantee if future versions will support to use sysstat. (Check the msdn site: https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql)

I wouldn't use any of them. I would prefer to check objects directly by name:

if exists (select 1 from sys.procedures where name = 'procedure_to_drop')

sys.procedures: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-procedures-transact-sql

If you have sql server 2016, you can use the new language elements to do the same depending on what you want:

Upvotes: 9

Related Questions