sql - SQL语法错误以外部方式创建过程

问题:如果将创建过程中不存在的IF添加为外部名称语句，则会收到语法错误...为什么？
如果我分别运行它们，这两个语句都可以正常工作...

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

最佳答案

因为CREATE PROCEDURE必须是批处理中的第一条语句。

首先删除proc或使用动态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')

您也可以颠倒逻辑

    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

关于sql - SQL语法错误以外部方式创建过程，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3157384/