如何在存储过程的后续IF语句中调用CTE？技术咨询

问题背景 #

需要编写存储过程实现以下逻辑：

• 通过CTE查询目标数据
• 根据CTE返回的行数是否为0，设置日志变量@COMMENT，记录"查询无数据"或"查询有数据"的执行情况
• 存储过程每日执行生成.txt文件，需通过日志避免业务部门误判执行出错

现有写法因CTE定义后直接跟IF流程控制语句违反SQL语法，导致执行失败。

错误原因 #

SQL中定义CTE（公用表表达式）后，必须立即紧跟SELECT、INSERT、UPDATE或DELETE语句，不能直接使用IF等流程控制语句，CTE仅在后续紧跟的那条语句中有效。

解决方案 #

方法一：将CTE结果存入临时表，再判断行数 #

把CTE的查询结果存入临时表，之后就可以自由使用IF语句判断临时表的行数：

CREATE PROCEDURE st_exemple
AS
    SET NOCOUNT ON
    -- 声明日志变量
    DECLARE @COMMENT NVARCHAR(100)

    -- 定义CTE并将结果插入临时表
    WITH CTE AS 
    (
        SELECT 
            TABEL1.ID_COTA AS [ID COTA],
            TABLE2.NM_CLIENT AS [NM CLIENT]
        FROM 
            -- 填入实际表关联逻辑
            TABEL1
            JOIN TABLE2 ON TABEL1.关联字段 = TABLE2.关联字段
        WHERE
            -- 填入实际过滤条件
            1=1
    )
    SELECT * INTO #TempCTE FROM CTE; -- 将CTE结果存入临时表

    -- 判断临时表行数
    IF (SELECT COUNT(*) FROM #TempCTE) = 0
    BEGIN
        SET @COMMENT = '查询无数据'
    END
    ELSE
    BEGIN
        SET @COMMENT = '查询有数据'
        -- 若需处理数据，可在此基于#TempCTE执行后续操作
    END

    -- 后续日志记录或文件生成逻辑示例
    PRINT @COMMENT;

    -- 清理临时表
    DROP TABLE IF EXISTS #TempCTE

END_PROC:
    RETURN

方法二：直接通过CTE统计行数赋值给变量（更简洁） #

无需临时表，直接在CTE后用SELECT语句统计行数并赋值给变量，再进行判断：

CREATE PROCEDURE st_exemple
AS
    SET NOCOUNT ON
    -- 声明日志变量和行数统计变量
    DECLARE @COMMENT NVARCHAR(100),
            @RowCount INT

    -- 定义CTE并统计行数
    WITH CTE AS 
    (
        SELECT 
            TABEL1.ID_COTA AS [ID COTA],
            TABLE2.NM_CLIENT AS [NM CLIENT]
        FROM 
            TABEL1
            JOIN TABLE2 ON TABEL1.关联字段 = TABLE2.关联字段
        WHERE
            1=1
    )
    SELECT @RowCount = COUNT(*) FROM CTE; -- 直接统计CTE行数到变量

    -- 根据行数设置日志
    IF @RowCount = 0
    BEGIN
        SET @COMMENT = '查询无数据'
    END
    ELSE
    BEGIN
        SET @COMMENT = '查询有数据'
        -- 若需处理原始数据，可重新执行CTE查询，或结合方法一的临时表方式
    END

    -- 后续日志记录或文件生成逻辑示例
    PRINT @COMMENT;

END_PROC:
    RETURN

注意事项 #

• 若后续需要使用CTE的原始数据进行业务处理，推荐使用方法一的临时表方式，避免重复执行CTE查询
• 确保变量@COMMENT已正确声明，存储过程中需提前定义该变量

内容的提问来源于stack exchange，提问作者Artur Carreira