AW is completely right about IN and parameter passing, but
If you're willing to consider not using IN construct in where ...
You could use PATINDEX in a where clause. I assume that you will pass a delimited list of values that have been selected from the multiline listbox to the SP. Do something like
select <cols>
from <join statement>
where
patindex(classification_id
You of course need to make sure that the classification_id's pattern in the database will be identified uniquely in the delimited string (for example: searching for 1 in a string that contains 10 will provide a result, but not the intended)
Main Topics
Browse All Topics
by: Arthur_WoodPosted on 2008-05-30 at 18:32:14ID: 21682378
if you want to pass multiple values, to use an IN clause in your WHERE, then you will need to build a dynamic SQL statement, and the use the EXEC operator in the Stored Procedure.
You will pass a comma delimited string ("choice1, choice2, choice3", and then have a user defined function in SQL Server that will parse the comma-delimited string into a set of separate values, that you can then use in the dynamic SQL that your SP will build and then execute.
The problem with your original approach is that the variable @class that you are using is seen as a single string "1,2,3", and not as separate values "1","2","3".
The In operator attempts to match a single classification_id (say 1) with "1,2,3", and finds no match, rather than searching the list of separate values "1","2","3", and finding a match on the first value. Do you see the difference?
"I have read many solutions involving sending to xml or creating a tmpTable or a function to split into comma sperated values. These seem rather longwinded or am I expecting too much for little effort." That is the only way to accomplish what you want, and yes, you are expecting too much for little effort.
AW