CODEWITHSUNDEEP
sqlsql-serverstored-procedures
MrBliz
MrBliz

Reputation: 5918

Incorrect Syntax Near SET. Expecting EXTERNAL

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

Upvotes: 5

Views: 12684

Answers (3)

JDoubleu7
JDoubleu7

Reputation: 1

Try the following:

CREATE OR ALTER PROCEDURE usp_SomeProcedure AS SELECT 'procedure created'

Upvotes: -1

Pரதீப்
Pரதீப்

Reputation: 93734

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

Upvotes: 12

Sarang
Sarang

Reputation: 7

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM sys.objects where name = 'ELMAH_GetErrorXml' and type ='P')
Drop Procedure [dbo].[ELMAH_GetErrorXml]
Go

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

GO
SET QUOTED_IDENTIFIER OFF
GO
 SET ANSI_NULLS ON
 GO

Upvotes: 0

Related Questions