I’m trying to create a stored procedure in SQL Server that accepts a list of indicators, executes a dynamic query for each indicator and combines the results into a temporary table. However, I’m encountering an error when trying to declare the temporary table:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 11, column: 24: Incorrect syntax near 'TABLE'.

The stored procedure looks like this:

CREATE PROCEDURE spProcessIndicators
(
    @Indicators NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @indicator NVARCHAR(50);

    -- Create the temporary table to store the results
    DECLARE @TempTable TABLE (
        [year] INT,
        [indicator] NVARCHAR(50),
        [socioEconomicLevel] NVARCHAR(50),
        [profession] NVARCHAR(50),
        [gender] NVARCHAR(50),
        [ageGroup] NVARCHAR(50),
        [n] FLOAT,
        [d] INT,
        [e] FLOAT,
        [e_s] FLOAT
    );

    -- Split the list of indicators
    DECLARE @IndicatorsTable TABLE (Value NVARCHAR(50));
    INSERT INTO @IndicatorsTable (Value)
    SELECT TRIM(value) FROM STRING_SPLIT(@Indicators, ',');

    -- Iterate over each indicator and execute the query
    DECLARE IndicatorCursor CURSOR FOR
    SELECT Value FROM @IndicatorsTable;

    OPEN IndicatorCursor;
    FETCH NEXT FROM IndicatorCursor INTO @indicator;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Build the dynamic SQL query for each indicator
        SET @sql = N'
        WITH Calculations AS (
            SELECT
                a.[year],
                ''' + @indicator + N''' AS [indicator],
                a.[gender],
                CASE
                    WHEN a.[age] BETWEEN 0 AND 14 THEN ''00 to 14''
                    WHEN a.[age] BETWEEN 15 AND 39 THEN ''15 to 39''
                    WHEN a.[age] BETWEEN 40 and 64 THEN ''40 to 64''
                    WHEN a.[age] BETWEEN 65 and 74 THEN ''65 to 74''
                    WHEN a.[age] >= 75 THEN ''75 or more'' END AS [ageGroup],
                [socioEconomicLevel] AS [socioEconomicLevel],
                SUBSTRING([professionCode], 1, 1) AS [profession],
                ' + QUOTENAME(@indicator) + N' AS [n],
                1 AS [d],
                AVG(' + QUOTENAME(@indicator) + N' * 1.0) OVER (PARTITION BY a.[year], a.[age], a.[gender]) AS [pond],
                AVG(' + QUOTENAME(@indicator) + N' * 1.0) OVER (PARTITION BY a.[year], a.[age]) AS [pond_s]
            FROM [schema].[MainTable] a
        )

        SELECT
            [year],
            [indicator],
            [socioEconomicLevel],
            [profession],
            [gender],
            [ageGroup],
            SUM([n]) AS [n],
            SUM([d]) AS [d],
            SUM([pond] * [d]) AS [e],
            SUM([pond_s] * [d]) AS [e_s]
        FROM Calculations
        GROUP BY
            [year],
            [indicator],
            [socioEconomicLevel],
            [profession],
            [gender],
            [ageGroup];';

        -- Insert the results into the temporary table
        INSERT INTO @TempTable
        EXEC sp_executesql @sql;

        FETCH NEXT FROM IndicatorCursor INTO @indicator;
    END

    CLOSE IndicatorCursor;
    DEALLOCATE IndicatorCursor;

    -- Select all combined results
    SELECT * FROM @TempTable;
END;

I’m declaring the Temp Table as expected, I don’t know why it gives the error at line 11. Maybe the semicolons are not correct?

Any help to identify and fix the problem would be greatly appreciated. Thanks!