stevegray
4Penny.net
Points: 55409

3/1/2012 11:39:19 AM

How to parse a comma separated string into a SQL table, Part 2

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

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.

 

4Penny.net
Version: All
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3