什么是优选的方法来确定存储过程是否存在？

Question

我过去在存储过程部署脚本中经常使用以下内容：

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 ...

我之前不知道有一种可以通过类型查找对象ID的OBJECT_ID负载。

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

似乎使用那个重载可以省去OBJECTPROPERTY的查找。我想知道只使用OBJECT_ID重载并放弃OBJECTPROPERTY的查找是否有任何不利影响。

- C B

---

SQL对象都共享相同的唯一命名空间。如果存在该名称的对象，则OBJECT_ID返回该对象的ID。OBJECTPROPERTY确认该对象实际上是存储过程，因为可能会有一个名为[SPROC_NAME]的表或视图（如果有，则无法使用该名称创建存储过程）。 - RBarryYoung

如果指定了第二个参数，OBJECT_ID 只会返回一个非空值，如果存在该类型的对象。（确保指定模式，不知道如果它是一个模式中的表和另一个模式中的过程会发生什么...） - Philip Kelley

Answer 1

我更喜欢使用这种形式，并尽可能停止使用OBJECT*元数据函数，因为它们甚至可以在松散的隔离级别下被阻塞：

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

- Aaron Bertrand

---

我倾向于删除已存在的内容，然后重新创建，以确保我始终获取最新版本... - Mitch Wheat

@Mitch ，CREATE存根允许真正的主体始终是ALTER，即使存储过程尚不存在，这样就不必全部使用动态SQL。 - Aaron Bertrand

我之前也使用过DROP和CREATE脚本，但最近有人指出，这会影响与该过程相关联的create_date。如果您不关心这些历史信息/元数据，则没有问题，但至少要意识到这一点。 - Mr Moose

有趣的；我喜欢这个！我不知道阻塞问题。DROP/CREATE也会丢失安全设置（如果您没有将它们包含在脚本中）。 - C B

Answer 2

您的条件可以更简短一些：

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

另一种方法：

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

- Devart

Answer 3

我的首选方式是：

：

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

- Mark Kremers

Answer 4

我的意见 -

使用带有第二个参数的object_id方法。这样可以使代码更易于阅读。

- Rawheiser

---

sys.objects既不是系统表也不是向后兼容的视图，它是一个目录视图。我认为使用OBJECT_ID()是否更易读很主观，更不用说许多这样的元数据函数不遵守隔离级别语义（这意味着即使您设置了read_uncommitted或snapshot，它们也可能会被阻塞）。 - Aaron Bertrand