Create a stored procedure that iterates over another stored procedure?

Question

I have a question about using stored procedures 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;```

Answer 1

• Firstly, you need to split the parameter into multiple parameters using comma as separator. For this, you can use string_split function.
• Store the result in a table variable called @indicators.
• Then iterate through the indicators using a cursor and execute the sp_indicators stored procedure for each one.

Below is the code for sp_indicators_multi stored procedure.

CREATE PROC z_sch.sp_indicators_multi
(
    @IDS VARCHAR(MAX)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);


    DECLARE @indicators TABLE (indicator VARCHAR(50));
    INSERT INTO @indicators (indicator)
    SELECT value FROM STRING_SPLIT(@IDS, ',');


    DECLARE @indicator VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT indicator FROM @indicators;
    OPEN cur;
    FETCH NEXT FROM cur INTO @indicator;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE z_sch.sp_indicators @indicator;
        FETCH NEXT FROM cur INTO @indicator;
    END;
    CLOSE cur;
    DEALLOCATE cur;
END;

demo - fiddle

Update:

Azure synapse doesn't support cursor-based approach. Below is the code which works in azure synapse environment.

CREATE PROCEDURE z_sch.sp_indicators_multi
(
    @IDS VARCHAR(MAX)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    -- Split the input parameter into individual indicators
    create table #indicators  (indicator VARCHAR(50));
    INSERT INTO #indicators (indicator)
    SELECT value FROM STRING_SPLIT(@IDS, ',');

    DECLARE @indicator VARCHAR(50)
    DECLARE @counter int = 1
    DECLARE @recordCount int = (SELECT COUNT(1) from #indicators)
    WHILE @counter <= @recordCount 
    BEGIN  
      SET @indicator = (SELECT [indicator]
      FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY [indicator]) AS RN
      FROM #indicators) as T
      WHERE RN = @counter)
      EXECUTE z_sch.sp_indicators @indicator;
      SET @counter = @counter + 1
    END;
    drop table #indicators
END;