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