Re: Conditional Where clause in stored proc

use the DBMS_SQL package to define your SQL at runtime.

bgeake_at_my-deja.com wrote:

> How can a select procedure have its Where clause set conditionally
> depending on a paramater value?
>
> Depending on the value, I need to use the clause "And complete_by_date
> <= Sysdate" or "And complete_by_date <= Sysdate + 7" or "And
> Months_Between(complete_by_date,Sysdate) <= 1".
>
> I've tried:
> If parameter = value1 Then And complete_by_date <= Sysdate;
> Elsif parameter = value2 Then And complete_by_date <= Sysdate + 7;
> Elsif parameter = value3 Then And Months_Between
> complete_by_date,Sysdate) <= 1;
> End If;
>
> which gives lots of PLS-00103 errors. So what's the real way to do this?
>
> TIA,
>
> Bill.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jul 29 1999 - 10:12:02 CDT