sql-server - 在 SQL Server 2012 的存储过程中执行多条语句和 CTE？

Question

我是 SQL Server 2012 的新手，我正在尝试创建一个存储过程，它应该:

根据参数从表中删除以前的数据，然后

在该表上插入新数据。

但我需要使用 CTE 出于性能和其他原因(请不要挂断电话，我必须这样做，请相信我的话)。

如果 CTE 查询单独存在于存储过程中，则它可以正常工作，但我无法让 SP 使用两个子句。

尝试创建过程时出现错误，提示我应该在 CTE 之前使用分号。如果我添加分号，SQL Server 也会提示它。它让我发疯， 请帮忙 !

我要删除/插入的表:

CREATE MYTABLE ( APPUSER NVARCHAR(15), DATA NVARCHAR(100) )

简化的存储过程(无分号):

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER )

    WITH CTE AS (
        SELECT DATA
        FROM SOURCETABLE
        WHERE ( TYPE = @TYPE )
    )
    INSERT INTO MYTABLE
    SELECT
        @SOMEUSER,
        DATA
    FROM CTE 
END

没有分号的错误信息:

[Error Code: 319, SQL State: S1000] Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

请注意，尽管这是实际查询的简化版本，但错误完全相同。我确实尝试了上面的代码，但没有任何运气:(

带分号的存储过程(与上面相同的查询，为了简洁只显示分号):

(...)
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER ); /* Semicolon */

    WITH CTE AS (    
(...)

分号错误:

[Error Code: 102, SQL State: 42000] Incorrect syntax near ')'.

我尝试将 DELETE 子句包含在它自己的事务中，这也不起作用，总是得到两个错误之一。

任何指针将不胜感激，谢谢!!!

Answer 1

你需要放一个';'在 'With' 关键字之前，您的代码如下所示

CREATE PROCEDURE P1 ( @SOMEUSER NVARCHAR(15), @TYPE INTEGER) AS
BEGIN
    DELETE FROM MYTABLE WHERE ( APPUSER=@SOMEUSER )

    ;WITH CTE AS (
        SELECT DATA
        FROM SOURCETABLE
        WHERE ( TYPE = @TYPE )
    )
    INSERT INTO MYTABLE
    SELECT
        @SOMEUSER,
        DATA
    FROM CTE 
END

作为 CTE 的经验法则，如果您的 CTE 上方有任何可执行语句，请始终以分号开头。