如何通过参数指定存储过程返回对应CTE的查询结果？

问题场景 #

有一个存储过程，通过CTE生成两个数据集：

• CTE_Time_Between_Tasks：粒度级数据，包含Task_ID、Priority、Time_From_T1_to_T2、Time_From_T2_to_T3列
• CTE_Avg_Time_Btwn_Tasks：聚合级数据，包含Priority、Avg_Time_From_T1_to_T2、Avg_Time_From_T2_to_T3列

希望通过@OutputChoice参数（VARCHAR(3)）指定返回结果：传入'gra'返回粒度数据，传入'avg'返回聚合数据。原代码结构如下：

DECLARE @OutputChoice AS VARCHAR(3)

WITH CTE1 AS (...)
,CTE2 AS (...)
,CTE_Time_Between_Tasks AS (...)
,CTE_Avg_Time_Btwn_Tasks AS (...)
SELECT 
CASE WHEN @OutputChoice = 'gra' THEN (SELECT * FROM CTE_Time_Between_Tasks)
    WHEN @OutputChoice = 'avg' THEN (SELECT * FROM CTE_Avg_Time_Btwn_Tasks)
    ELSE NULL
    END AS RESULTS

执行时触发错误：

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

单独查询两个CTE均正常，需要修正该错误。

错误原因 #

CASE表达式只能返回单个值，而分支里用SELECT *返回了多列的结果集，不符合SQL对CASE的语法要求，因此报错。

解决方案 #

方法1：使用IF ELSE分支（逻辑清晰，推荐） #

将CTE定义放在分支外部，根据参数选择返回对应的CTE结果：

DECLARE @OutputChoice AS VARCHAR(3)

WITH CTE1 AS (...)
,CTE2 AS (...)
,CTE_Time_Between_Tasks AS (...)
,CTE_Avg_Time_Btwn_Tasks AS (...)
IF @OutputChoice = 'gra'
    SELECT * FROM CTE_Time_Between_Tasks
ELSE IF @OutputChoice = 'avg'
    SELECT * FROM CTE_Avg_Time_Btwn_Tasks
ELSE
    SELECT NULL AS RESULTS -- 也可返回自定义提示信息

方法2：使用UNION ALL加过滤条件 #

通过UNION ALL合并两个结果集，再根据参数过滤出对应数据。注意需保证两个结果集的列数、数据类型兼容，缺失列用NULL填充：

DECLARE @OutputChoice AS VARCHAR(3)

WITH CTE1 AS (...)
,CTE2 AS (...)
,CTE_Time_Between_Tasks AS (...)
,CTE_Avg_Time_Btwn_Tasks AS (...)
SELECT 
    Task_ID,
    Priority,
    Time_From_T1_to_T2,
    Time_From_T2_to_T3,
    NULL AS Avg_Time_From_T1_to_T2,
    NULL AS Avg_Time_From_T2_to_T3
FROM CTE_Time_Between_Tasks
WHERE @OutputChoice = 'gra'

UNION ALL

SELECT
    NULL AS Task_ID,
    Priority,
    NULL AS Time_From_T1_to_T2,
    NULL AS Time_From_T2_to_T3,
    Avg_Time_From_T1_to_T2,
    Avg_Time_From_T2_to_T3
FROM CTE_Avg_Time_Btwn_Tasks
WHERE @OutputChoice = 'avg'

这种方法会返回包含所有列的统一结构结果集，未选中的数据集列将填充NULL，适合需要固定返回格式的场景。

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