Stored Procedures -Optional Parameters.

[balachandar]

Hi All,

I have got the following stored procedure.
PROCEDURE Rpt_Manu_Sales_Sum_Bu_Prc(v_Data_Ref_Cursor IN OUT Rpt_Stored_Proc_Pkg.Data_Ref_Cursor,START_DATE IN DATE,END_DATE IN DATE,REP_NAME IN VARCHAR2,AGENCY_REGION_DESC IN VARCHAR2 )
AS
BEGIN
OPEN v_Data_Ref_Cursor FOR
SELECT
VEND_NEDA_NUM MFG_CODE,
VEND_NAME MFG_NAME,
START_DATE,
END_DATE

FROM
TABLEA I,
TABLEB IL
WHERE
I.INVC_SEQ_NUM = IL.INVC_SEQ_NUM
AND I.INVC_DT >= START_DATE
AND IL.INVC_DT <= END_DATE
AND I.REP_NAME = REP_NAME
;

END Rpt_Manu_Sales_Sum_Bu_Prc;

Now in the above procedure START_DATE and END_DATE will always be passed as parameters. But REP_NAME will be an optional parameter. SO I will have to include optional parameter only if it is passed. Please do help me to constuct the stored procedure so that it can handle optional parameters as well. Currently the Query will work fine only if all parameters are passed.

Regards
Balachandar Ganesan.

 

[sem]

..
AND (REP_NAME is NULL or I.REP_NAME = REP_NAME)

OR

if REP_NAME is NULL then
OPEN v_Data_Ref_Cursor FOR
SELECT
VEND_NEDA_NUM MFG_CODE,
VEND_NAME MFG_NAME,
START_DATE,
END_DATE

FROM
TABLEA I,
TABLEB IL
WHERE
I.INVC_SEQ_NUM = IL.INVC_SEQ_NUM
AND I.INVC_DT >= START_DATE
AND IL.INVC_DT <= END_DATE
;
else
OPEN v_Data_Ref_Cursor FOR
SELECT
VEND_NEDA_NUM MFG_CODE,
VEND_NAME MFG_NAME,
START_DATE,
END_DATE

FROM
TABLEA I,
TABLEB IL
WHERE
I.INVC_SEQ_NUM = IL.INVC_SEQ_NUM
AND I.INVC_DT >= START_DATE
AND IL.INVC_DT <= END_DATE
AND I.REP_NAME = REP_NAME
;
end if;

Regards, Dima

 

[lewisp]

You will also need to declare your procedure in one of two ways:

1) [tt]PROCEDURE Rpt_Manu_Sales_Sum_Bu_Prc(v_Data_Ref_Cursor IN OUT Rpt_Stored_Proc_Pkg.Data_Ref_Cursor,
START_DATE IN DATE,
END_DATE IN DATE,
REP_NAME IN VARCHAR2 DEFAULT NULL,
AGENCY_REGION_DESC IN VARCHAR2 )[/tt]

The call to the procedure will need a non-positional parameter passing to AGENCY_REGION_DESC, eg:

[tt]Rpt_Manu_Sales_Sum_Bu_Prc(<cursor>,
'12-JAN-2003',
'12-FEB-2003',
agency_region_desc=>'a desc');[/tt]

2) Swap the positions of the last two parameters:

[tt]PROCEDURE Rpt_Manu_Sales_Sum_Bu_Prc(v_Data_Ref_Cursor IN OUT Rpt_Stored_Proc_Pkg.Data_Ref_Cursor,
START_DATE IN DATE,
END_DATE IN DATE,
AGENCY_REGION_DESC IN VARCHAR2,
REP_NAME IN VARCHAR2 DEFAULT NULL)
[/tt]

 

[balachandar]

Thanks for the suggestions. But What if there are more optional parameters ?. In some cases I do have 4 optional parameters. In that case I need to declare more number of cursors depending on what was passed ?. Is there any other to handle that ?.

Regards
Balachandar Ganesan.

 

[sem]

You may create WHERE clause dynamically:
...


BASE_QUERY VARCHAR2(2000) :=
'SELECT VEND_NEDA_NUM MFG_CODE,'||
'VEND_NAME MFG_NAME,START_DATE,END_DATE '||
'FROM TABLEA I, TABLEB IL '
'WHERE I.INVC_SEQ_NUM = IL.INVC_SEQ_NUM '||
'AND I.INVC_DT >= START_DATE '||
'AND IL.INVC_DT <= END_DATE ';

BEGIN
...
if REP_NAME is NOT NULL then
BASE_QUERY := BASE_QUERY ||
'AND I.REP_NAME = '''||REP_NAME||'''';
end if;
...

OPEN v_Data_Ref_Cursor FOR BASE_QUERY;
...
END;


Regards, Dima