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

Question

Not sure if I phrased that right to display my intention but, I have the following TSQL query:

SELECT *
FROM Table 
WHERE Amount_USD >= @init AND Amount_USD < @init2

@init is equal to the base searched value, and @init2 is equal to the base value plus 1. A juxtaposition of this code is made to read negative values if they are available. (Through if else)

This is intended to get all the decimal values of a given value, including that value. This is going to be part of a long chain of where criteria's, searching the date, searching the ID, and searching all decimal values of a given value as above. Problem is, this only works for one input, I intend on making it work for multiple entered values.

Now, what I'm having trouble with is how to word it if I wish to create an IN (list) version of this query so that I can search multiple decimal values of any given value.

Ex: Search amount of 100 120 130. Program picks up the following values from the backend: 100.12, 120.14, 100.99, 130, 130.544 / Etc.

I believe it can be done with the OR function though, like so:

SELECT * 
FROM Table 
WHERE (Amount_USD >= @init AND Amount_USD < @init2) 
   OR (Amount_USD >= @init3 AND Amount_USD < @init4) 
   OR (Amount_USD >= @init5 AND Amount_USD < @init6)

As you can see above, I come across the problem wherein I create these parameters from nothing which I would like to avoid, therefore an IN might be preferable... now that I think about it, the parameters might always be a problem... is there a solution to this?

For example, I define my parameters like so:

da.SelectCommand.Parameters.AddWithValue("@init", init);
da.SelectCommand.Parameters.AddWithValue("@init2", init2);

Say, the input value count increases to 4, or 9. I don't know how to scale this.

Edit: I forgot to mention that this is written in Tsql, interfacing an access database through a windows form. As such, many sql specific functions or methods may not work in my case.

Edit2: Posted the wrong SQL query above, should be correct now...

Possible duplicate of [LINQ query that searches for tuples](https://stackoverflow.com/questions/45053565/linq-query-that-searches-for-tuples) — mjwills, Jul 26 '17 at 06:51
If a reply has answered your question, it would be nice if you marked it as answered. This not only helps other users with similar problems, but also improves the reputation of the person who answered. — Jonathan Willcock, Jul 26 '17 at 23:24

Jonathan Willcock · Accepted Answer · 2017-07-26T07:49:56.543

Please note that Access does not accept T-SQL. This means that a scaleable answer, where the number of parameters is undefined is not possible in the same way as you can achieve with T-SQL (through XML parameters for example). There is however a simple alternative. You create a for loop in your c# to dynamically build your SQL statement. The following should give you a clue:

int[] paramArray = new int[] { 100, 102, 104, 106 };
StringBuilder sB = new StringBuilder("SELECT * FROM TABLE WHERE");
for (int i = 0; i < paramArray.Length - 1; i++)
{
    if (i > 0)
    {
        sB.Append(" OR");
    }
    sB.Append(" (Amount_USD >= " + paramArray[i].ToString() + " AND Amount_USD < " + (paramArray[i] + 1).ToString() + ")");
}
string sQL = sB.ToString();

EDIT

Adapting @Gelion suggestion to work with Access you would get:

int[] paramArray = new int[] { 100, 102, 104, 106 };
StringBuilder sB = new StringBuilder("SELECT * FROM TABLE WHERE IIF(Amount_USD < ROUND(Amount_USD),ROUND(Amount_USD)-1,ROUND(Amount_USD)) IN (");
for (int i = 0; i < paramArray.Length - 1; i++)
{
    if (i > 0)
    {
        sB.Append(", ");
    }
    sB.Append(paramArray[i].ToString());
}
string sQL = sB.ToString() + ")";

IN() usually executes quicker when the ORs exceed a certain number (wish I knew what this number was!).

[Little Bobby Tables](https://xkcd.com/327/) will love your way of building statements — Sir Rufo, Jul 26 '17 at 06:20
@SirRufo Haha. In this case though it is perfectly safe, as the values being inserted within the loop are type-safe ints. The OP was specifically about providing an indeterminate number of parameters to an Access database. Do you know a better way? — Jonathan Willcock, Jul 26 '17 at 06:29
Yes, my query is actually completely built through a string builder, however, their values are grabbed through parameters as it should be at the end of the method. Put this way, it would seem that creating parameters as I was was the wrong solution as I would need more for each input, Jonathan's solution seems to address that. — Aroueterra, Jul 26 '17 at 06:38

score 0 · Answer 2 · answered Jul 26 '17 at 06:17

0

I am not sure if Access accepts this, tsql surely will do:

select *
from Table t
    join (values(100),(120),(130))initVal(fromValue) 
       on initVal.fromValue <= t.Amount_USD and initVal.fromValue + 1 > t.Amount_USD

answered Jul 26 '17 at 06:17

avb

1,743
1
13
23

The simple answer is no, this will not work with Access – Jonathan Willcock Jul 26 '17 at 07:22

Gelion · Answer 3 · 2017-07-26T06:47:05.333

-1

If I understood you correctly you can siplify your query like so:

select * FROM Table WHERE round(Amount_USD, 2, 1) in (@init1,@init3,@init5)

and if Acess acepts func Round with one parameter

select * from Table WHERE case when Amount_USD<round(Amount_USD,0) then  round(Amount_USD,0)-1 else round(Amount_USD,0) end  in (@init1,@init3,@init5)

version without any parameters, and you can add new values during construction of your sql query

select * from Table WHERE case when Amount_USD<round(Amount_USD,0) then  round(Amount_USD,0)-1 else round(Amount_USD,0) end  in (100,200,300)

This parameterless version is not safe you have to be carefull and check numbers before concate them

edited Jul 26 '17 at 06:47

answered Jul 26 '17 at 06:20

Gelion

34
1
4

This unfortunately doesn't cover the problem of making it scalable, so if I have a new inputted number, I would need to somehow create an accompanying parameter. – Aroueterra Jul 26 '17 at 06:35
I ve just modified my answer to make it more scalable – Gelion Jul 26 '17 at 06:50
Regrettably Access does not have CASE. You need to use IIF. Bit more messy – Jonathan Willcock Jul 26 '17 at 07:03
There is a lot of paranoia about SQL Injection, much of it ill-informed. C# is a type safe language. If the only elements that you are effectively injecting are number-types, then you do not need to check. Moreover, in this case, there is extra protection: Access will reject multiple statements in one query. Even if these were string insertions, because it is Access, you need not worry so much about the worst cases of SQL injection. – Jonathan Willcock Jul 26 '17 at 07:11
Also Access ROUND does only take two parameters (not three) – Jonathan Willcock Jul 26 '17 at 07:14

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

3 Answers3