Parameters in query with in clause?

Question

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

For whom ever is still interested. I did it in Firebird 2.5 using another stored procedure inspired by this post.

How to split comma separated string inside stored procedure?

CREATE OR ALTER PROCEDURE SPLIT_STRING (
    ainput varchar(8192))
RETURNS (
    result varchar(255))
AS
DECLARE variable lastpos integer;
DECLARE variable nextpos integer;
DECLARE variable tempstr varchar(8192);
BEGIN
  AINPUT = :AINPUT || ',';
  LASTPOS = 1;
  NEXTPOS = position(',', :AINPUT, LASTPOS);
  WHILE (:NEXTPOS > 1) do
  BEGIN
    TEMPSTR = substring(:AINPUT from :LASTPOS for :NEXTPOS - :LASTPOS);

    RESULT = :TEMPSTR;
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = position(',', :AINPUT, LASTPOS);
    suspend;
  END

END

When you pass the SP the following list

CommaSeperatedList = 1,2,3,4

and call

SELECT * FROM SPLIT_STRING(:CommaSeperatedList)

the result will be :

RESULT
1
2
3
4

And can be used as follows:

SELECT * FROM MyTable where MyKeyField in ( SELECT * FROM SPLIT_STRING(:CommaSeperatedList) )

Answer 2

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 3

Maybe you should wite it like this:

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

Answer 4

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 5

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 6

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.

Answer 7

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 8

SELECT * FROM MATABLE WHERE MT_ID IN (:MYPARAM) instead of using MYPARAM with :, use parameter name.

like SELECT * FROM MATABLE WHERE MT_ID IN (SELECT REGEXP_SUBSTR(**MYPARAM,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(MYPARAM, '[^,]+', 1, LEVEL) IS NOT NULL))**

MYPARAM- '368134,181956'

Answer 9

There is one trick to use reversed SQL LIKE condition.

You pass the list as string (VARCHAR) parameter like '~12~23~46~567~'

Then u have query like where ... :List_Param LIKE ('%~' || CAST( NumField AS VARCHAR(20)) || '~%')

Answer 10

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 11

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 12

CREATE PROCEDURE TRY_LIST (PARAM_LIST VARCHAR(255)) RETURNS (FIELD1....) 
AS 
BEGIN
 /* Check if :PARAM_LIST begins with colon "," and ands with colon "," 
    the list should look like this --> eg. **",1,3,4,66,778,33,"**          
    if the format of list is right then GO if not just add then colons
 */
 IF (NOT SUBSTRING(:PARAM_LIST FROM 1 FOR 1)=',') THEN PARAM_LIST=','||PARAM_LIST;
 IF (NOT SUBSTRING(:PARAM_LIST FROM CHAR_LENGTH(:PARAM_LIST) FOR 1)=',') THEN PARAM_LIST=PARAM_LIST||',';

/* Now you are shure thet :PARAM_LIST format is correct */
/ * NOW ! */
FOR SELECT * FROM MY_TABLE WHERE POSITION(','||MY_FIELD||',' in :PARAM_LIST)>0 
INTO :FIELD1, :FIELD2 etc... DO
BEGIN
  SUSPEND;
END

END

How to use it.

SELECT * FROM TRY_LIST('3,4,544,87,66,23')
or SELECT * FROM TRY_LIST(',3,4,544,87,66,23,') 
if the list have to be longer then 255 characters then just change the part of header f.eg. like PARAM_LIST VARCHAR(4000)