sql - SET 附近的语法不正确。期待外部

Question

我正在尝试检查存储过程是否存在，如果不存在，则创建它。

我收到错误

Incorrect Syntax Near SET. Expecting EXTERNAL

代码:

IF (NOT EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.ROUTINES 
             WHERE ROUTINE_SCHEMA = 'dbo' 
             AND  ROUTINE_NAME = 'ELMAH_GetErrorXml'))
BEGIN
    CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml]
    (
        @Application NVARCHAR(60),
        @ErrorId UNIQUEIDENTIFIER
    )
    AS

        SET NOCOUNT ON

        SELECT 
            [AllXml]
        FROM 
            [ELMAH_Error]
        WHERE
            [ErrorId] = @ErrorId
        AND
            [Application] = @Application
END

Answer 1

您收到该错误，因为这不是检查对象(过程)是否存在的权利，如果不存在则创建它。尝试这样的事情。

IF NOT EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = Object_id(N'[dbo].[ELMAH_GetErrorXml]')
                      AND type IN ( N'P', N'PC' ))
  BEGIN
      EXEC('CREATE PROCEDURE [dbo].[Elmah_geterrorxml] (@Application NVARCHAR(60),
                                                        @ErrorId     UNIQUEIDENTIFIER)
            AS
              BEGIN
                  SET NOCOUNT ON

                  SELECT [AllXml]
                  FROM   [ELMAH_Error]
                  WHERE  [ErrorId] = @ErrorId
                         AND [Application] = @Application
              END')
  END

但简单的方法是

IF EXISTS (SELECT *
           FROM   INFORMATION_SCHEMA.ROUTINES
           WHERE  ROUTINE_SCHEMA = 'dbo'
                  AND ROUTINE_NAME = 'ELMAH_GetErrorXml')
  BEGIN
      DROP PROCEDURE [dbo].[Elmah_geterrorxml]
  END

GO

CREATE PROCEDURE [dbo].[Elmah_geterrorxml] (@Application NVARCHAR(60),
                                            @ErrorId     UNIQUEIDENTIFIER)
AS
  BEGIN
      SET NOCOUNT ON

      SELECT [AllXml]
      FROM   [ELMAH_Error]
      WHERE  [ErrorId] = @ErrorId
             AND [Application] = @Application
  END