Re: Help with constructing a 'select-from' procedure

off the top of my head, i think you can do this (performance notwithstanding ..)

for every 'a' that the stored proc has, add a line to your where clause that looks like (ax = t.ix or ax is null). in your example with a1..a2 and t1..t2 your cursor would be defined as

open rc for select * from test_table t
  where (t.i1 = a1 or a1 is null)
  and (t.i2 = a2 or a2 is null)

i think that should work. basically ix can be anything if ax is null.  if ax is not null, i1 must equal it. does that help?

dkash78_at_yahoo.com (Dr. No) wrote in message news:<2faeea43.0202050019.58d58d1d_at_posting.google.com>...
> 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 - 13:51:11 CST