Re: Optional (ignored) parameters in stored procedures (PL/SQL)? How to?

How about overloading your function

eg

Function doit(memberid in T_UM_BASE.UMBS_MEMB%TYPE, state in T_UM_BASE.UMBS_MEMB%TYPE);
Function doit(memberid in T_UM_BASE.UMBS_MEMB%TYPE);

with appropriate selects in each one.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"Kurta" <submit_at_galleus.com> wrote in message
news:efcb1994.0401090716.5da3c1de_at_posting.google.com...


> Hi,

>

> State is a parameter of the function. When it's NULL, we don't need to

query for it.


>

> Is there a way to replace this peace of code:

> .

> .

> IF (state IS NOT NULL) THEN

>   OPEN resultCursor FOR

>     SELECT UMET_ID

>       FROM T_UM_BASE

>       WHERE UMBS_MEMB = memberId

>         AND UMBS_OBJ_STT = state;

> ELSE

>   OPEN resultCursor FOR

>     SELECT UMET_ID

>       FROM T_UM_BASE

>       WHERE UMBS_MEMB = memberId;

> END IF;

> .

> .

> ...with a more elegant solution __without using dynamic SQL__ in a stored

function?


>

> This may work in MSSQL but doesn't work in Oracle:

> OPEN resultCursor FOR

>   SELECT UMET_ID

>     FROM T_UM_BASE

>     WHERE UMBS_MEMB = memberId

>       AND UMBS_OBJ_STT = COALESCE(state, UMBS_OBJ_STT);

>

> Any help?

>

> Thanks