Where Clause With Pivot Table?

I am using this article for a dynamic pivot table: http://www.sqlteam.com/item.asp?ItemID=2955





I am trying it in Query analyzer and when I try to put a Where clause into the query I get an error: Invalid column name 'CCFIF'.





I run just the Select statement and it returns values without error.





What am I doing wrong??








declare @select varchar(8000), @sumfunc varchar(100),@pivot varchar(100), @table varchar(100)





select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID


LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.UserInfo.LocationID = CCFIF) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name'


select @sumfunc= 'COUNT(ActionID)'


select @pivot='UserName'


select @table= 'UserInfo'





DECLARE @sql varchar(8000), @delim varchar(1)


SET NOCOUNT ON


SET ANSI_WARNINGS OFF





EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')


EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '


+ @pivot + ' Is Not Null')





SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )





SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )


WHEN 0 THEN '' ELSE '''' END


FROM tempdb.information_schema.columns


WHERE table_name='##pivot' AND column_name='pivot'





SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +


stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '


+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot





DROP TABLE ##pivot





SELECT @sql=left(@sql, len(@sql)-1)


SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')





EXEC (@select)


SET ANSI_WARNINGS ON





Thanks!