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

From: Kurta <submit_at_galleus.com>
Date: 9 Jan 2004 07:16:47 -0800
Message-ID: <efcb1994.0401090716.5da3c1de@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 Received on Fri Jan 09 2004 - 09:16:47 CST