Dynamic SQL breaks statement 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.
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