CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
C B
C B

Reputation: 647

What is the preferred way to identify whether a stored procedure exists

I've historically used the following as part of a stored procedure deployment script:

IF NOT EXISTS (SELECT * FROM sys.objects O 
   WHERE O.[object_id] = OBJECT_ID(N'[dbo].[SPROC_NAME]') 
   AND OBJECTPROPERTY(O.[object_id],N'IsProcedure') = 1)

    EXEC('CREATE PROCEDURE [dbo].[SPROC_NAME] AS')
GO

ALTER PROCEDURE [dbo].[SPROC_NAME]
AS
BEGIN ...

I did not know about the overload to OBJECT_ID which takes a type for object ID lookup, for example:

OBJECT_ID(N'[dbo].[SPROC_NAME]', N'P')

It seems like using that overload would make the OBJECTPROPERTY lookup unnecessary.

I'd like to know if there is any downside to using just the OBJECT_ID overload and dropping the OBJECTPROPERTY lookup.

Upvotes: 5

Views: 298

Answers (4)

Rawheiser
Rawheiser

Reputation: 1220

my $.02 -

Use the object_id method with the 2nd parameter. It makes the code easier to read.

Upvotes: 0

Devart
Devart

Reputation: 121902

Your condition can be a bit shorter:

    IF OBJECT_DEFINITION(OBJECT_ID('dbo.Procedure_Name', 'P')) IS NULL
    BEGIN
        EXEC sys.sp_executesql 'CREATE PROCEDURE dbo.Procedure_Name AS'
    END

Another way:

    IF OBJECT_ID('dbo.Procedure_Name', 'P') IS NULL
    BEGIN
        EXEC sys.sp_executesql 'CREATE PROCEDURE dbo.Procedure_Name AS'
    END

Upvotes: 1

Mark Kremers
Mark Kremers

Reputation: 1789

My prefered way is :

  if object_id('x') is not null
  begin
    drop procedure x
  end
  go
  create procedure x ...
  ...

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I prefer this form, and have stopped using the OBJECT* metadata functions as much as possible, since they can block even at loose isolation levels:

IF NOT EXISTS 
(
   SELECT 1 FROM sys.procedures AS p
     INNER JOIN sys.schemas AS s
     ON p.[schema_id] = s.[schema_id]
     WHERE p.name = N'Procedure_Name' AND s.name = N'dbo'
)
BEGIN
  EXEC sp_executesql N'CREATE PROCEDURE dbo.Procedure_Name AS';
END

Upvotes: 3

Related Questions