Sql-server – 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.