>I have a dropdown list in a page.
>This list as the multiple selection option set.
>So when a request the form data I get something like:
>
>data1, data2, data3
>
>no problem with that.
>The problem is with my query.
>I have a stored proc that take a parameter.
>This stored proc is doing a Select statement based on the parameter with a IN statement.
>Explanation:
>
>SELECT
> *
>FROM
> Test
>WHERE
> Col.a IN (@parameter)
>
>And, of course, the "@parameter" as the "data1, data2, data3" taken from my asp
>page.
>It's not working, unless I only have 1 selection from my dropdown list.
>Is there a syntax specific or a special way of resolving my problem?
You probably need to parse the parameter into the various strings the single parameter is composed of. If not, it's like executing the statement like this:
Select * From Test Where Col.a In ('data1, data2, data3')
Where most likely the query will not find matching rows.
Or, construct the SQL inside the SP and execute it with sp_executesql:
Declare @SQLSt Varchar(100)
Set @SQLSt = 'Select * From Test Where Col.a In (' + @parameter + ')'
Exec sp_executesql @SQLSt
Also, if the parameters are expected as string you should enclose them within single quotes.
Sp_executesql also allows for passing parameters, which will probably help performance. Check sp_executesql in the SQL books online.
HTH