How to pass list of values as a parameter to OleDbCommand

Question

I want to execute query

SELECT id, name FROM MyTable WHERE (id, name) IN ((1, 'One'), (2, 'Two'))

using OleDbConnection and OleDbCommand. Is it possible to pass the contents of IN clause as a parameter to command? If yes then what value should I pass a parameter value? List, IEnumerable or something else? If not then is it possible to pass the list of single values like for query

SELECT id, name FROM MyTable WHERE id IN (1, 2)

why not just assign the whole of the IN Clause to a string in code behind and then make your query this "SELECT id, name FROM MyTable WHERE (id, name) IN " + InClauseString — jgok222, Sep 15 '14 at 16:31
http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause — Steve, Sep 15 '14 at 16:32
Just to note that your first snippet is not a valid SQL Syntax — Steve, Sep 15 '14 at 16:38

score 1 · Accepted Answer · answered Sep 15 '14 at 16:33

As long as I know you can't. I was trying something similar in the past and the only way I found was to create an stored procedure which receives a comma separated string and create the query dynamically, calling sp_execute at the end with the resulting string.

score 0 · Answer 2 · answered Sep 15 '14 at 16:35

Take a look at http://www.codeproject.com/Tips/231217/Parameters-SqlCommand-vs-OledbCommand-and-OdbcComm

You would be responsible for writing the SQL of course. SQL does not take anonymous objects for an IN parameter. You must specify what you are looking for in a single field.

Select * from table where field in (1,2,3) --Assuming field is an integer

For example.

How to pass list of values as a parameter to OleDbCommand

2 Answers2