SQL Syntax error CREATE PROCEDURE AS EXTERNAL

Question

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

score 1 · Accepted Answer · answered Jul 01 '10 at 11:49

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

score 0 · Answer 2 · answered Jul 01 '10 at 11:48

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.

SQL Syntax error CREATE PROCEDURE AS EXTERNAL

2 Answers2