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

you could use t.i1 = nvl(a1,t.i1). If a1 is null you get t.i1 = t.i1 which is true for every record.

By the way, your syntax is non-Oracle at some points:

• you cannot specify ':= null' in the parameter section
• you have to specify IN, OUT or IN OUT for every parameter
• checking for null is done with IS NULL or IS NOT NULL and not '<> null'.

Jaap.

On 5 Feb 2002 00:19:27 -0800, dkash78_at_yahoo.com (Dr. No) wrote:

>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 Wed Feb 06 2002 - 12:26:39 CST