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)
as
declare comma char = ','
declare val varchar(9) = '';
declare suspend_now char = 'N';
declare currentchar char;
begin
while (numargs > 0) do
begin
currentchar = substring(inputstr from 1 for 1);
if (( currentchar = comma)
or (currentchar = '')
or (currentchar = ' ')
or (currentchar is null)) then
suspend_now = 'Y';
else
val = val || currentchar;
if (suspend_now = 'Y') then
begin
suspend_now = 'N';
execute statement 'SELECT r1 FROM t1 WHERE x1 = ' || val into :r;
suspend;
val = '';
numargs = numargs - 1;
if (currentchar = comma) then
inputstr = substring(inputstr from 2); -- jump over comma
end
inputstr = substring(inputstr from 2);
end

./hb
end