sql - Using a comma-separated parameter in an IN clause

Question

I have 'param1, param2, parma3' coming from SSRS to a stored procedure as a varchar parameter: I need to use it in a query's IN clause but then need to change its format like this first:

select *
from table1
where col1 in('param1', 'param2', 'param3')

What is the best way to reformat the parameter without creating functions and parameter tables?

Answer 1

Try this one, Just need to add commas at the beginning and at the end of @params string.

Declare @params varchar(100) Set @params = ',param1,param2,param3,'

Select * from t
where CHARINDEX(','+cast(col1 as varchar(8000))+',', @params) > 0

SQL FIDDLE