simply change the
' AND CustomerName IN (' + @CUSTNAME + ')'
into
' WHERE CustomerName IN (' + @CUSTNAME + ')'
as in the
SELECT @SQL = @SQL + ' AND CustomerName IN (' + @CUSTNAME + ')'
the SQL Server will combine the @SQL with the new AND statement behind. Therefore the AND statement is not append behind the WHERE inside the pivot but after the pivot p. Instead of using AND, WHERE should be used.