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

Question

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

On the Configure Data Source tab, if I hardcode the SELECT .... WHERE field1 in ('AAA', 'BBB', 'CCC') 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 @session, Control or querystring; for example SELECT .... WHERE field1 in @SESSION result is always empty.

I did another experiment, reducing the content of the parameter to a single value, it works well.

in short, if I hardcode a string of values, it works, if I pass a variable with a single value, it works, but if I pass varialbe with two values; he did not pass.

Pls advise if I am wrong or this is a known bug.

BR SDIGI

Answer 1

It works. Not sure how effective it is.

 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 SQL IN Clause

Which ultimately connects (via a confusing route) with this final answer:

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

Answer 3

If you move on to using a stored procedure, you can use this method , which I discussed regarding how to do this in SQL.