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).