Retrieving tokenized params

I am implementing a multivariable search on a couple of tables in a DB. In
the front end the client requires that one of the criteria
of the search supports multiple choices (e.g. in a ListBox control). To my
knowledge, the only way to satisfy this requirement is to pass the back end
a string where the selected values are separated by some delimiter, e.g. in
the mock SP below. My qst: 1) is it logically possible to achieve
what the client wants in such a SP and if so, 2) how to pass every token
from @.param_array to the last AND condition. Any other suggestions/examples
to solve the problem are welcome.
TIA
CREATE PROCEDURE dbo.usp_MultivarSearch
(
@.param_1 <type> ,
@.param_2 <type> ,
..
@.param_n <type> ,
@.param_array varchar(256) -- e.g. 'val1|val2|val3|...|valn'
)
as
select <fields>
from <joined tables>
where <some_field> = @.param_1
and <some_other_field> = @.param_2
and ( <field_in_question> = val1 or <field_in_question> = val2
or ... <field_in_question> = valn )
GOSee the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan|||You can use the charindex() function for this.
"alto" <altodorov@.hotmail.com> wrote in message
news:OhdIKFfvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I am implementing a multivariable search on a couple of tables in a DB. In
>the front end the client requires that one of the criteria
> of the search supports multiple choices (e.g. in a ListBox control). To my
> knowledge, the only way to satisfy this requirement is to pass the back
> end
> a string where the selected values are separated by some delimiter, e.g.
> in the mock SP below. My qst: 1) is it logically possible to achieve
> what the client wants in such a SP and if so, 2) how to pass every token
> from @.param_array to the last AND condition. Any other
> suggestions/examples
> to solve the problem are welcome.
> TIA
> CREATE PROCEDURE dbo.usp_MultivarSearch
> (
> @.param_1 <type> ,
> @.param_2 <type> ,
> ...
> @.param_n <type> ,
> @.param_array varchar(256) -- e.g. 'val1|val2|val3|...|valn'
> )
> as
> select <fields>
> from <joined tables>
> where <some_field> = @.param_1
> and <some_other_field> = @.param_2
> and ( <field_in_question> = val1 or <field_in_question> = val2
> or ... <field_in_question> = valn )
> GO
>
>