Parameters in query with in clause ?

Question

Hi all,

I want to use parameter for query like this :

SELECT * FROM MATABLE
WHERE MT_ID IN (368134, 181956)

so I think about this

SELECT * FROM MATABLE
WHERE MT_ID IN (:MYPARAM)

but it doesn't work...

Is there a way to do this ?

I actually use IBX and Firebird 2.1

I don't know how many parameters in IN clause.

Answer 1

Maybe you should wite it like this:

SELECT * FROM MATABLE
WHERE MT_ID IN (:MYPARAM1 , :MYPARAM2)

Answer 2

If you are using Oracle, then you should definitely check out Tom Kyte's blog post on exactly this subject (link).

Following Mr Kyte's lead, here is an example:

SELECT *
  FROM MATABLE
 WHERE MT_ID IN
       (SELECT TRIM(substr(text, instr(text, sep, 1, LEVEL) + 1,
                           instr(text, sep, 1, LEVEL + 1) -
                            instr(text, sep, 1, LEVEL) - 1)) AS token
          FROM (SELECT sep, sep || :myparam || sep AS text
                  FROM (SELECT ',' AS sep
                          FROM dual))
        CONNECT BY LEVEL <= length(text) - length(REPLACE(text, sep, '')) - 1)

Where you would bind :MYPARAM to '368134,181956' in your case.

Answer 3

The answer from Yurish is a solution in two out of three cases:

• if you have a limited number of items to be added to your in clause
• or, if you are willing to create parameters on the fly for each needed element (you don't know the number of elements in design time)

But if you want to have arbitrary number of elements, and sometimes no elements at all, then you can generate SLQ statement on the fly. Using format helps.

Answer 4

I ended up using a global temporary table in Firebird, inserting parameter values first and to retrieve results I use a regular JOIN instead of a WHERE ... IN clause. The temporary table is transaction-specific and cleared on commit (ON COMMIT DELETE ROWS).

Answer 5

I don't think it's something that can be done. Are there any particular reason why you don't want to build the query yourself?

I've used this method a couple of times, it doesn't use parameters though. It uses a stringlist and it's property DelimitedText. You create a IDList and populate it with your IDs.

Query.SQL.Add(Format('MT_ID IN (%s)', [IDList.DelimitedText]));

Answer 6

You might also be interested in reading the following:
http://www.sommarskog.se/dynamic_sql.html
and
http://www.sommarskog.se/arrays-in-sql-2005.html

Covers dynamic sql with 'in' clauses and all sorts. Very interesting.

Answer 7

Here is a technique I have used in the past to get around that 'IN' statement problem. It builds an 'OR' list based on the amount of values specified with parameters (unique). Then all I had to do was add the parameters in the order they appeared in the supplied value list.

var  
  FilterValues: TStringList;
  i: Integer;
  FilterList: String;
  Values: String;
  FieldName: String;
begin
  Query.SQL.Text := 'SELECT * FROM table WHERE '; // set base sql
  FieldName := 'some_id'; // field to filter on
  Values := '1,4,97'; // list of supplied values in delimited format
  FilterList := '';
  FilterValues := TStringList.Create; // will get the supplied values so we can loop
  try
    FilterValues.CommaText := Values;

    for i := 0 to FilterValues.Count - 1 do
    begin
      if FilterList = '' then
        FilterList := Format('%s=:param%u', [FieldName, i]) // build the filter list
      else
        FilterList := Format('%s OR %s=:param%u', [FilterList, FieldName, i]); // and an OR
    end;
    Query.SQL.Text := Query.SQL.Text + FilterList; // append the OR list to the base sql

    // ShowMessage(FilterList); // see what the list looks like. 
    if Query.ParamCount <> FilterValues.Count then
      raise Exception.Create('Param count and Value count differs.'); // check to make sure the supplied values have parameters built for them

    for i := 0 to FilterValues.Count - 1 do
    begin
      Query.Params[i].Value := FilterValues[i]; // now add the values
    end;

    Query.Open;  
finally
  FilterValues.Free;  
end;

Hope this helps.

Answer 8

Parameters are placeholders for single values, that means that an IN clause, that accepts a comma delimited list of values, cannot be used with parameters.

Think of it this way: wherever I place a value, I can use a parameter.

So, in a clause like: IN (:param)

I can bind the variable to a value, but only 1 value, eg: IN (4)

Now, if you consider an "IN clause value expression", you get a string of values: IN (1, 4, 6) -> that's 3 values with commas between them. That's part of the SQL string, not part of a value, which is why it cannot be bound by a parameter.

Obviously, this is not what you want, but it's the only thing possible with parameters.