c# - Creating an IN function out of a query, so that it can handle multiple inputs -

not sure if phrased right display intention but, have following tsql query:

select * table  amount_usd >= @init , amount_usd < @init2  

@init equal base searched value, , @init2 equal base value plus 1. juxtaposition of code made read negative values if available. (through if else)

this intended decimal values of given value, including value. going part of long chain of criteria's, searching date, searching id, , searching decimal values of given value above. problem is, works 1 input, intend on making work multiple entered values.

now, i'm having trouble how word if wish create in (list) version of query can search multiple decimal values of given value.

ex: search amount of 100 120 130. program picks following values backend: 100.12, 120.14, 100.99, 130, 130.544 / etc.

i believe can done or function though, so:

select *  table  (amount_usd >= @init , amount_usd < @init2)     or (amount_usd >= @init3 , amount_usd < @init4)     or (amount_usd >= @init5 , amount_usd < @init6) 

as can see above, come across problem wherein create these parameters nothing avoid, therefore in might preferable... think it, parameters might problem... there solution this?

for example, define parameters so:

da.selectcommand.parameters.addwithvalue("@init", init); da.selectcommand.parameters.addwithvalue("@init2", init2); 

say, input value count increases 4, or 9. don't know how scale this.

edit: forgot mention written in tsql, interfacing access database through windows form. such, many sql specific functions or methods may not work in case.

edit2: posted wrong sql query above, should correct now...

please note access not accept t-sql. means scaleable answer, number of parameters undefined not possible in same way can achieve t-sql (through xml parameters example). there simple alternative. create loop in c# dynamically build sql statement. following should give clue:

int[] paramarray = new int[] { 100, 102, 104, 106 }; stringbuilder sb = new stringbuilder("select * table where"); (int = 0; < paramarray.length - 1; i++) {     if (i > 0)     {         sb.append(" or");     }     sb.append(" (amount_usd >= " + paramarray[i].tostring() + " , amount_usd < " + (paramarray[i] + 1).tostring() + ")"); } string sql = sb.tostring(); 

edit

adapting @gelion suggestion work access get:

int[] paramarray = new int[] { 100, 102, 104, 106 }; stringbuilder sb = new stringbuilder("select * table iif(amount_usd < round(amount_usd),round(amount_usd)-1,round(amount_usd)) in ("); (int = 0; < paramarray.length - 1; i++) {     if (i > 0)     {         sb.append(", ");     }     sb.append(paramarray[i].tostring()); } string sql = sb.tostring() + ")"; 

in() executes quicker when ors exceed number (wish knew number was!).