BBLN BBLN - 1 year ago 55
SQL Question

Using IN operator with Stored Procedure Parameter

I am building a website in ASP.NET 2.0, some description of the page I am working about:
ListView displaying a table (of posts) from my access db, and a ListBox with Multiple select mode used to filter rows (by forum name, value=forumId).
I am converting the ListBox selected values into a List, then running the following query.




SELECT * FROM sp_feedbacks WHERE forumId IN ([@Q])

The problem is, well, it doesn't work. Even when I run it from MSACCESS 2007 with the string 1,4, "1","4" or "1,4" I get zero results. The query works when only one forum is selected. (In (1) for instance).

    So I guess I could use WHERE with many OR's but I would really like to avoid this option.
    Another solution is to convert the DataTable into list then filter it using LINQ, which seems very messy option.

Thanks in advance,

Answer Source

When you have:

col in ('1,4')

This tests that col is equal to the string '1,4'. It is not testing for the values individually.

One way to solve this is using like:

where ','&@Q&',' like '*,'&col&',*'

The idea is to add delimiters to each string. So, a value of "1" becomes ",1,"in the column. A value of "1,4" for @Q becomes ",1,4,". Now when you do the comparison, there is no danger that "1" will match "10".

Note (for those who do not know). The wildcard for like is * rather than the SQL standard %. However, this might differ depending on how you are connecting, so use the appropriate wildcard.