Passing a comma delimited string of parameters to a stored proc

Hello,

I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.

But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

I would recommend Dr. Lisa to you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

|||

Hi,

You could create a function that would convert your string into a table and then use this table in you stored procedure.

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

HTH,

|||

I usually use MVP Jens Suesmeyer's SPLIT function; it can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17