CODEWITHSUNDEEP
sqlsyntaxsyntax-errorsqlclrclrstoredprocedure
Stefan Steiger
Stefan Steiger

Reputation: 82276

SQL Syntax error CREATE PROCEDURE AS EXTERNAL

Question: If I add IF not exists to a create procedure as external name statement, I get a syntax error... why? Both statements work fine if I run them separately...

IF  NOT EXISTS 
(
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'PriceSum') 
    AND type in (N'P', N'PC')
)
CREATE PROCEDURE PriceSum(@sum int OUTPUT)
     AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].PriceSum

Upvotes: 0

Views: 4018

Answers (2)

SQLMenace
SQLMenace

Reputation: 135111

because the CREATE PROCEDURE has to be the first statement in a batch.

either drop the proc first or use dynamic SQL

IF  NOT EXISTS 
(
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'PriceSum') 
    AND type in (N'P', N'PC')
)
exec('CREATE PROCEDURE PriceSum(@sum int OUTPUT)
     AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].PriceSum')

you can also reverse the logic

    IF  EXISTS 
    (
        SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'PriceSum') 
        AND type in (N'P', N'PC')
    )

    DROP PROCEDURE PriceSum
    GO --this ends the batch, so the create statement below is fine

    CREATE PROCEDURE PriceSum(@sum int OUTPUT)
AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].PriceSum
  GO

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453628

Try

IF  NOT EXISTS 
(
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'PriceSum') 
    AND type in (N'P', N'PC')
)

EXEC ('CREATE PROCEDURE PriceSum(@sum int OUTPUT)
     AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].PriceSum')

I don't think it likes stuff before CREATE PROCEDURE and in the same batch.

Upvotes: 0

Related Questions