Why do I need to declare variables twice: within and without the dynamic query?

I’m reworking an existing static query to be dynamic. Why am I needing to declare the variables twice? Once at the top of the file (for a subsequent, unchanging query) and again within the dynamic SQL statement. Is this something to do with how dynamic SQL behaves?

My code is this:

DECLARE @columns NVARCHAR(MAX) = ''
DECLARE @sql     NVARCHAR(MAX) = ''
DECLARE @Offset int = 0
DECLARE @Limit int = 10
DECLARE @TeamCode NVARCHAR(50) = 'Team1'

SELECT 
    @columns+=QUOTENAME(status_name) + ','
FROM 
    my_db.job_statuses
ORDER BY 
    id;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
DECLARE @Offset int = 0
DECLARE @Limit int = 10
DECLARE @TeamCode NVARCHAR(50) = ''Team1''
SELECT P.*, TSC.TOTAL_STATUS_COUNT FROM 
(
    SELECT * FROM   
    (
        SELECT 
            EJI.planning_item_id,
            S.status_name
        FROM my_db.jobs J
        INNER JOIN my_db.job_statuses S ON J.status = S.ID
        INNER JOIN my_db.extra_job_information EJI ON EJI.job_id = J.job_id
        WHERE (@TeamCode IS NULL OR ASSIGNED_TO LIKE @TeamCode)
        AND (Planning_item_id IS NOT NULL)
        GROUP BY status_name, planning_item_id, J.job_id 
    ) T 

    PIVOT(
        COUNT(status_name) 
        FOR status_name IN ('+ @COLUMNS +')
    ) AS PIVOT_TABLE
    ORDER BY planning_item_id
    OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY
    
) P
INNER JOIN
(
    SELECT
        EJI.planning_item_id,
        COUNT(J.status) TOTAL_STATUS_COUNT
    FROM my_db.extra_job_information EJI
    INNER JOIN my_db.jobs J ON J.JOB_ID = EJI.JOB_ID 
    WHERE (@TeamCode IS NULL OR ASSIGNED_TO LIKE @TeamCode)
    AND (Planning_item_id IS NOT NULL)
    GROUP BY planning_item_id
) TSC ON TSC.planning_item_id = P.planning_item_id';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

FYI I only have read access on ‘my_db’.
I used https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/ to pivot dynamic query

Answer

For the same reason that:

A variable is declared in a stored procedure
That procedure calls a second procedure
The variable declared in the first procedure is not accessible in the second procedure.

In other words: scope. The dynamic code is executed as it’s own batch, process, session… thing, I’m sorry but I don’t know the exact technical term as used by SQL.

Check out sp_ExecuteSQL’s support for parameters. It’s fussy and complex but will allow you to pass parameter values into (and out of!) your dynamic SQL.

Advertisement

Answer