In an earlier article we showed how to turn a comma separated list into a table.
I've been using that code for some time, especially when I needed to turn a multi value parameter from SSRS report into something that I can use.
Having done that a few times, it occurred to me that it might be easier to change it into a table value function - now there is no editing, I just include the function in my code and and we're off to the races.
In other words, SSRS gives you something like "apple, banana, pear"
We call the function like this:
select rs.*
from myReportSource rs
join dbo.f_Dyn_CommaSepListToTable('apple, bananna, pear') f on f.vchr100 = rs.fruitName
And all the work is done for us.
In the code above, 'apple, bannana, pear' come to as as the multi select parameter in SSRS. The function turns this into a table; that table is joined to the main report source and the report source is correctly filtered.