TSQL Passing MultiValued Reporting Services Parameter into Dynamic SQL

Question

Duplicate of: http://stackoverflow.com/questions/712443/tsql-varchar-string-manipulation-problem/712453#712453

I'm building a dynamic SQL statement out of parameters from a reporting services report. Reporting services passes MutiValue Parameters in a basic CSV format. For example a list of states may be represented as follows: AL,CA,NY,TN,VA

In a SQL statement this is OK:

WHERE customerState In (@StateList)

However, the dynamic variant isn't OK:

SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (' + @StateList + ') '

This is because it translates to (invalid SQL):

AND customerState IN (AL,CA,NY,TN,VA)

To process it needs something like this:

AND customerState IN ('AL','CA','NY','TN','VA')

Is there some cool expression I can use to insert the single quotes into my dynamic SQL?

Answer 1

This takes care of the middle:

SET @StateList = REPLACE(@StateList, ',', ''',''')

Then quote the edges:

SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (''' + @StateList + ''') '

Answer 2

REPLACE didn't work for me when used with IN for some reason. I ended up using CHARINDEX

WHERE CHARINDEX( ',' + customerState + ',', ',' + @StateList + ',' ) > 0