Dynamic SQL breaks statement

Question

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the query with dynamic sql it breaks. Here is the statement that works fine.

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(record_name) 
            FROM ams_data
            WHERE report_name = 'class_and_component_prices'
            AND commodity ='whey'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        record_name,
        value,
        date,
        commodity
    FROM 
       ams_data
        WHERE report_name = ''class_and_component_prices''
        AND commodity = ''whey''
) t 
PIVOT(
    max(value)  
    FOR record_name IN ('+ @cols +')
)  AS pivot_table ;';

EXECUTE sp.executesql @sql

And here is my version that takes parameters.

ALTER PROCEDURE  [dbo].[pivot_record]
(
    -- Add the parameters for the stored procedure here
    @tablename VARCHAR(max) = nass_data,
    @report_name VARCHAR(max) = class_and_component_prices,
    @commodity VARCHAR(max) = whey
)
AS
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
        DECLARE @cols AS NVARCHAR(MAX),
                @sql  AS NVARCHAR(MAX),
                @actualTable AS VARCHAR(MAX)

        SELECT @actualTable = QUOTENAME( TABLE_NAME )
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = @tablename

        SET @sql = '
        STUFF((
            SELECT distinct '','' + QUOTENAME(record_name) 
            FROM ' + @actualTable + ' 
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + ''' 
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
            ,1,1,'''')'        

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

        -- construct dynamic SQL
        SET @sql ='
        SELECT * FROM   
        (
            SELECT 
                record_name,
                value,
                date,
                commodity

            FROM 
               ' + @tablename + '
                WHERE report_name = ' + @report_name + '
                AND commodity = ' + @commodity + '
        ) t 
        PIVOT(
            max(value)  
            FOR record_name IN ('+ @cols +')
        )  AS pivot_table ;';

        -- execute the dynamic SQL
        EXECUTE sp_executesql @sql;

When I attempt to run this, I get an error from the first sql statement. I printed off the sql statement prior to attempting to execute and store the result in @cols to ensure that it is exactly the same as my first statement that worked.

I am assuming that some quirk with dynamic sql is causing the issue, but assistance would be appreciated.

EDIT: I figured out that I needed to include a SELECT before the STUFF clause, but I still do not get my intended result, now it just outputs an empty table instead of the proper pivoted data.

Answer 1

The first query works because the STUFF statement is not dynamic so the @cols variable is populated correctly using SET @cols = .

In your second query, you don't ever set a variable to the STUFF value, hence the error.

Original:

SET @sql = '
        STUFF((
            SELECT distinct '','' + QUOTENAME(record_name) 
            FROM ' + @actualTable + ' 
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + ''' 
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
            ,1,1,'''')'        

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

New:

SET @sql = 'SET @cols = STUFF((
            SELECT distinct '','' + QUOTENAME(record_name) 
            FROM ' + @actualTable + ' 
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + ''' 
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
            ,1,1,'''')'        

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

Answer 2

It's hard to debug without seeing the output of your 2nd @SQL or @cols. I'd start from there to debug, SELECT/PRINT the variable and copy-and-paste-run-it

or try different way of cols?

DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

FYI this is my PIVOT query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = '
select *
from 
(
  select SQLInstanceName, Date, node_name
  from #Final
  WHERE 1=1 AND  Has_Changed = ''Y''
) src
pivot
(
  MIN(node_name)
  for [Date] in ('  + @ColumnName + ')
) piv
order by 1
'
--SELECT @DynamicPivotQuery --debug
EXEC sp_executesql @DynamicPivotQuery