Subject Re: [firebird-support] parameter for the IN statement in a stored procedure
Author Helen Borrie
At 07:10 AM 26/01/2005 +0000, you wrote:

>Hi everybody,
>I want to pass a list of parameters to a stored procedure and I don't
>know how to. Can you help?
>My stored procedure is like this:
>.. SELECT r1 FROM t1 WHERE x1 = n1 INTO :r;
>I want to change it to something like this:
>.. SELECT r1 FROM t1 WHERE x1 IN (n1,n2,n3,n4,n5) INTO :r;
>Is there a way?
>What variable do I need to declare as a parameter for the parameters
>in side of the paranthesis?

You have to declare one input argument for each value. There is no such
data type as a "list" in SQL.

But you could pass a comma-separated string and the number of arguments in
your list, and use EXECUTE STATEMENT for each implicit OR condition, to get
your result set cumulatively, something like this:

create procedure blah(inputstr varchar(50), numargs smallint)
returns (r integer)
declare comma char = ','
declare val varchar(9) = '';
declare suspend_now char = 'N';
declare currentchar char;
while (numargs > 0) do
currentchar = substring(inputstr from 1 for 1);
if (( currentchar = comma)
or (currentchar = '')
or (currentchar = ' ')
or (currentchar is null)) then
suspend_now = 'Y';
val = val || currentchar;
if (suspend_now = 'Y') then
suspend_now = 'N';
execute statement 'SELECT r1 FROM t1 WHERE x1 = ' || val into :r;
val = '';
numargs = numargs - 1;
if (currentchar = comma) then
inputstr = substring(inputstr from 2); -- jump over comma
inputstr = substring(inputstr from 2);