Using IF EXISTS

When I work with stored procedures, I will always test if the procedure exists, drop if it does exist, and then create the stored procedure. This produces nice clean code which can be saved to source control & run at any time as needed. Note that the create code includes the grant permissions.

I had been using "the long version" of the IF EXISTS statement, for example:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME' AND [type] = N'P'
)

This does work just fine, but MSSQL does not allow more than one schema-scoped object (table, view, sp, udf) with the same name. So, testing for the type is not necessary. So we could use just this:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME'
)

However, there is a unique ID for every object & a built-in function OBJECT_ID() to get that ID, so this is all we need to test if an object exists:

IF OBJECT_ID('TESTME') IS NOT NULL