Sproc and unknown number of parameters 1

[dbrom]

Hi everyone!

Hope you can give me a hand with this little problem:

I am developing an advanced search page that searches for actors resumes on our website. On this page, I have a number of fields: drop-down lists, checkboxes, etc.

For example, I have a group of checkboxes that list sports that actors are capable of playing. There are about 30 checkboxes, and when checked, the value of each checkbox is added to a string (soccer,football,karate,volleyball,etc). The problem is, I do not know exactly how many of these will be checked, but I need to develop a stored procedure that would get parameters from my ASP page and then return the results.

Here is my question:
Is it possible inside a stored procedure to take this string as a varchar parameter and then transform this string into a series of
colName LIKE '%soccer%' AND colName LIKE'%football%' AND ... ?

I also thought about extracting each of values from comma-separated string inside ASP code and then passing all thirty as parameters having default values, but along with other fields on the search page that would create a stored procedure with more than a hundred parameters, plus this approach is not scalable: each additional checkbox will require SQL code modifications.

Any ideas? <Dmitriy>
dbrom@crosswinds.net

 

[tlbroadbent]

Various users have posted procedures to parse a delimited string at SWYNK.com. Check this page.

http://www.swynk.com/sqlscripts/misc7.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.