SQL variable declared and used inside the Dynamic code in SSMS

Question

I have some dynamic code in my stored procedure; in the first part, a variable is being assigned, when I am using that variable later in the dynamic code, it is not working as expected.

Relevant block of the code is as follows:

SET @SQL += N' DECLARE @YearValues AS NVARCHAR(MAX);
        SELECT @YearValues = COALESCE(@YearValues + '', '', '''') + QUOTENAME(Y.Years)
        FROM (   SELECT DISTINCT BT.Years
                 FROM #BaseTable AS BT) AS Y
        ORDER BY Y.Years;
        
        SET @YearValues = Right(@YearValues,ABS(len(@YearValues)-2));

            SELECT PVT.Region
              , PVT.prdName
              , PVT.Type
              , PVT.Unit
              , PVT.ShareValue
              , @YearValues 
            FROM (
                SELECT BT.Region
                  , BT.prdName
                  , BT.Unit
                  , BT.Type
                  , BT.Years
                FROM #BaseTable AS BT
                WHERE EXISTS (SELECT 1
                              FROM #TypeSelected AS TS
                              WHERE TS.Type = BT.Type)
            ) AS Source
            PIVOT (
                MAX(ShareValue) FOR Years IN (@YearValues)
            ) AS PVT
           ';

EXEC sys.sp_executesql @stmt = @SQL

I tried to create #BaseTable as a global temp table, it works fine but as per my company policy I shouldn't use global temp tables. Is there any workaround for this?

Answer 1

You have a number of issues here:

• You are not actually dynamically injecting the column names, you are just using it directly in a static query. You need to move the column aggregation outside the dynamic query, then inject it.
• Do not use variable coalescing to aggregate, it can cause incorrect results. Use STRING_AGG or FOR XML instead.
• You are missing the ShareValue column in the inner query you are pivoting.
• Once you have pivoted, the two pivot columns disappear, you cannot select them on the outside.

DECLARE @YearValues AS NVARCHAR(MAX);

SELECT @YearValues = 
    STRING_AGG(QUOTENAME(Y.Years), ', ') WITHIN GROUP (ORDER BY Y.Years)
FROM (
   SELECT DISTINCT
     BT.Years
   FROM #BaseTable AS BT
) AS Y;
        
SET @SQL = '
SELECT
    PVT.Region
  , PVT.prdName
  , PVT.Type
  , PVT.Unit
  , ' + @YearValues + '
FROM (
    SELECT
          BT.Region
        , BT.prdName
        , BT.Unit
        , BT.Type
        , BT.Years
        , BT.ShareValue
    FROM #BaseTable AS BT
    WHERE EXISTS (SELECT 1
        FROM #TypeSelected AS TS
        WHERE TS.Type = BT.Type
    )
) AS Source
PIVOT (
    MAX(ShareValue) FOR Years IN (
        ' + @YearValues + '
    )
) AS PVT;
';

EXEC sys.sp_executesql @stmt = @SQL