SQL Server中创建多指标输入的sp_indicators_multi存储过程方案

需求概述 #

已创建单指标存储过程z_sch.sp_indicators，传入单个指标（如IDS001）会返回43437行15列的结果。现在需要创建z_sch.sp_indicators_multi，接收逗号分隔的多个指标参数，返回所有单指标结果的并集，总行数为43437 * n（n为指标数量）。

方案1：按你的思路实现（循环调用单存储过程） #

严格遵循你提出的步骤，适合不想修改原有单指标存储过程的场景：

代码实现 #

CREATE PROCEDURE z_sch.sp_indicators_multi
(
    @IDSList VARCHAR(MAX) -- 逗号分隔的指标列表，如'IDS001, IDS002, IDS015, IDS022'
)
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表存储结果，结构与sp_indicators返回结果一致
    CREATE TABLE #TempResults
    (
        [year] INT,
        [indicator] VARCHAR(50),
        [nse4] VARCHAR(50),
        [nse4c] VARCHAR(50),
        [ist] VARCHAR(50),
        [env] VARCHAR(50),
        [nac] VARCHAR(50),
        [irreg] VARCHAR(50),
        [prof] VARCHAR(50),
        [gender] VARCHAR(50),
        [age] VARCHAR(50),
        [n] DECIMAL(18,2),
        [d] INT,
        [e] DECIMAL(18,2),
        [e_s] DECIMAL(18,2)
    );

    -- 拆分指标列表，遍历每个指标
    DECLARE @IDS VARCHAR(50);
    DECLARE cur CURSOR FOR
        SELECT TRIM(value) AS IDS
        FROM STRING_SPLIT(@IDSList, ',')
        WHERE TRIM(value) <> ''; -- 过滤空值

    OPEN cur;
    FETCH NEXT FROM cur INTO @IDS;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用单指标存储过程，将结果插入临时表
        INSERT INTO #TempResults
        EXEC z_sch.sp_indicators @IDS;

        FETCH NEXT FROM cur INTO @IDS;
    END;

    CLOSE cur;
    DEALLOCATE cur;

    -- 返回最终结果
    SELECT * FROM #TempResults;

    -- 清理临时表
    DROP TABLE #TempResults;
END;

说明 #

• 使用STRING_SPLIT拆分逗号分隔的参数（SQL Server 2016及以上版本支持），若为旧版本需替换为自定义字符串拆分函数。
• 游标用于遍历每个指标，循环调用sp_indicators并将结果追加到临时表。
• SET NOCOUNT ON用于减少执行过程中的消息输出，提升性能。

方案2：优化方案（直接生成动态联合查询） #

循环调用存储过程会带来额外性能开销，更高效的方式是直接生成包含所有指标的UNION ALL动态SQL，一次性执行：

代码实现 #

CREATE PROCEDURE z_sch.sp_indicators_multi
(
    @IDSList VARCHAR(MAX)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @IDS VARCHAR(50);

    -- 遍历拆分后的指标，生成每个指标对应的查询语句，用UNION ALL连接
    DECLARE cur CURSOR FOR
        SELECT TRIM(value) AS IDS
        FROM STRING_SPLIT(@IDSList, ',')
        WHERE TRIM(value) <> '';

    OPEN cur;
    FETCH NEXT FROM cur INTO @IDS;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接单个指标的查询语句（复用sp_indicators中的逻辑）
        SET @SQL += N'
        WITH CTE AS (
            SELECT
                a.[year],
                ' + QUOTENAME(@IDS, '''') + N' AS [indicator],
                a.[gender],
                CASE
                        WHEN a.[age] BETWEEN 0 AND 14 THEN ''00 a 14''
                        WHEN a.[age] BETWEEN 15 AND 39 THEN ''15 a 39''
                        WHEN a.[age] BETWEEN 40 AND 64 THEN ''40 a 64''
                        WHEN a.[age] BETWEEN 65 AND 74 THEN ''65 a 74''
                        WHEN a.[age] >= 75 THEN ''75 o mes'' END AS [age],
                [nse4],
                SUBSTRING([professio_c], 1, 1) AS [prof],
                [nac],
                [irreg],
                [env],
                [ist],
                [nse4c],
                ' + QUOTENAME(@IDS) + N' AS [n],
                1 AS [d],
                AVG(' + QUOTENAME(@IDS) + N' * 1.0) OVER (PARTITION BY a.[year], a.[age], a.[gender]) AS [pond],
                AVG(' + QUOTENAME(@IDS) + N' * 1.0) OVER (PARTITION BY a.[year], a.[age]) AS [pond_s]
            FROM [z_sch].[indicadors_ind] a
            JOIN [z_sch].[abs_ist_entorn] b
            ON a.[abs_c] = b.[codi_abs]
        )
        SELECT
            [year],
            [indicator],
            [nse4],
            [nse4c],
            [ist],
            [env],
            [nac],
            [irreg],
            [prof],
            [gender],
            [age],
            SUM([n]) AS [n],
            SUM([d]) AS [d],
            SUM([pond] * [d]) AS [e],
            SUM([pond_s] * [d]) AS [e_s]
        FROM CTE
        GROUP BY
            [year],
            [indicator],
            [nse4],
            [nse4c],
            [ist],
            [env],
            [nac],
            [irreg],
            [prof],
            [gender],
            [age]';

        -- 不是最后一个指标的话，添加UNION ALL
        FETCH NEXT FROM cur INTO @IDS;
        IF @@FETCH_STATUS = 0
        BEGIN
            SET @SQL += N' UNION ALL ';
        END;
    END;

    CLOSE cur;
    DEALLOCATE cur;

    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END;

说明 #

• 直接复用原sp_indicators中的查询逻辑，为每个指标生成对应的查询块，用UNION ALL连接成完整SQL语句。
• 避免多次调用存储过程的开销，性能比方案1更优，尤其当指标数量较多时。
• 同样依赖STRING_SPLIT，旧版本需替换为自定义拆分函数。

使用示例 #

调用方式如下：

EXECUTE z_sch.sp_indicators_multi 'IDS001, IDS002, IDS015, IDS022';

该语句会返回173748行（43437*4）15列的结果集。

内容的提问来源于stack exchange，提问作者C. Moreno