Parameters and the IN operator?

[Mervin Williams]

Is there a way to use an input parameter to populate an IN operator (ie. "IN
(val1, val2, val3)") in a Where clause?

I have a data adapter whose SelectCommand reads:
"Select contact_id, firstname, lastname from Contact where contact_id
in (<val1>, <val2>, ...)"

I need to fill the list of values for the IN operator from a list of values
that I have retrieved in my code earleir. The goal here is to use an input
parameter that is an array of values used
to populate the IN operator in the where clause; so that the values "val1",
"val2" will be automatically enumerated from the parameter array.

Do you know whether this is possible?

Mervin Williams

 

[Nicholas Paldino [.NET/C# MVP]]

Mervin,

I don't think that this is possible. I think that for the in operator,
you will have to create the string yourself (or place the result in a temp
cursor on the server and then join to that cursor in your subsequent
selects).

Hope this helps.

 

[Miha Markic [MVP C#]]

Hi Mervin,

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

 

[William Ryan eMVP]

Mervin:

I wrote a brief article on this, but you can't do it directly b/c the
parameter is escaped. It's very doable though...

Take a look at this.. http://www.knowdotnet.com/articles/temptables.html