Incorrect Syntax Near SET. Expecting EXTERNAL

Question

I'm trying to check if a stored procedure exists, and then if it doesn't, create it.

I'm getting the error

Incorrect Syntax Near SET. Expecting EXTERNAL

Code:

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

You got that error because that is not the right for checking the existence of a object(procedure) and if not exists then creating it . try something like this.

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

But the simple way is

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