I’m new to stored procedures in SQL and I have a question about using them iteratively. I created a stored procedure called sp_indicators that accepts an indicator as a parameter and returns a table with 43437 rows and 15 columns, regardless of the indicator provided.

I call it like this:

EXECUTE z_sch.sp_indicators 'IDS001';

Now, I need to create another stored procedure called sp_indicators_multi that accepts multiple indicators as parameters and returns the union of the result tables. The resulting table should also have 15 columns, but a total of 43437 * n rows, where n is the number of indicators introduced. For example, the following line should return a (173748, 15) table:

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

The idea that I have is:

1. Create a temp table
2. Iterate through the input parameter
3. For each of the indicators, execute the stored procedure sp_indicators and append the result to the temp table
4. Once the iteration is over, return the full temp table

How can I achieve this in SQL Server? I appreciate any suggestions or code examples that can help me solve this problem.

Below I paste the code used to build the sp_indicators procedure:

CREATE PROCEDURE z_sch.sp_indicators
(
    @IDS VARCHAR(50)
)

AS

BEGIN
    DECLARE @sql NVARCHAR(MAX);

    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];';

    -- Execute the dynamic query
    EXEC sp_executesql @sql;
END;```