Help with constructing a 'select-from' procedure

Hi,

I need to write a procedure that based on values of parameters does selection from some table. It goes something like this

procedure test (a1 integer := null, a2 integer := null, rc IN OUT return-cursor)
begin
 open rc for select * from test_table t where t.i1 = a1 and t.i2 = a2; end;

Last parameter of the procedure is an in-out cursor variable that works as a pointer to the result range. But this is not too important. The problem is that if any of the arguments are null I need to select the whole range (i.e if a1 is null i should ommit it in my select statement:
select * from test_table t wgere t.i2 = a2 or
select * from test_table t where t.i1 <> null and t.i2 = a2.

I could write bunch of if statements and based on test for null open cursor differently. The problem is that I have about 20 parameters so to go through all of the cases I need 20! (factorial) brunches.

I would appreciate any help on this, even if the solution is dynamic. Received on Tue Feb 05 2002 - 02:19:27 CST