SQL "SELECT IN (Value1, Value2...)" with passing variable of values into GridView

Question

I have a strange encounter when creating a GridView using SELECT..WHERE..<field> IN (value1, val2...).

In the "Configure datasource" tab, if i hard code the values SELECT .... WHERE field1 in ('AAA', 'BBB', 'CCC'), the system works well.

However, if I define a new parameter and pass in a concatenated string of values using a variable; be it a @session, Control or querystring; e.g. SELECT .... WHERE field1 in @SESSION the result is always empty.

I did another experiment by reducing the parameter content to only one single value, it works well.

in short, if I hardcode a string of values, it works, if I pass a variable with single value only, it works, but if i pass a varialbe with two values; it failed.

Pls advise if I have make any mistake or it is a known bug.

BR SDIGI

Answer 1

This works. Not sure how efficient it is though.

CREATE PROCEDURE [dbo].[get_bars_in_foo]
    @bars varchar(255)
AS
BEGIN
    DECLARE @query AS varchar(MAX)
    SET @query = 'SELECT * FROM [foo] WHERE bar IN (' + @bars + ')'
    exec(@query)
END

-- exec [get_bars_in_foo] '1,2,3,4'

Answer 2

Take a look at the answer to this question (which is very similar to yours)

Parameterize an SQL IN clause

Which ultimately links (via a convoluted route) to this definitive answer:

http://www.sommarskog.se/arrays-in-sql.html

Answer 3

If you go to using a stored procedure, you can use this method, which I discussed in regards to how to do it in SQL.