Ignoring parameters if not required in Oracle stored procedure

Question

I have a stored procedure where there are 4-5 parameters to filter query based on those parameters. I have created the procedure and data is also getting filtered, but I want to implement one thing that if sometimes user doesn't want to pass one of those parameters then the data is not getting filtered. Below is my query.

    CREATE OR REPLACE PROCEDURE FETCH_CONS_REPORT_NEW(
        P_STATENAME IN VARCHAR2,
        P_CITYNAME  IN VARCHAR2,
        P_FROMDATE  IN DATE,
        P_TODATE    IN DATE,
        TBLOUT OUT SYS_REFCURSOR )
    AS
      v_query_string    VARCHAR2 (9000);
      v_where_condition VARCHAR2 (9000);
    BEGIN
      OPEN TBLOUT FOR SELECT a.changerequestid, a.changerequestnumber, a.networktype, a.statename, a.cityname, a.description, a.createdon, a.lastmodifiedon, a.lastmodifiedby,a.band, b.sap_id, b.site_type, b.cr_category, b.latitude, b.longitude, b.approve_reject, b.CREATION_SAPID, b.APPROVED_BY, b.BACKHAUL, b.CR_JUSTIFICATIONS, b.CREATED_DATE FROM changerequests a inner join tbl_pre_post_hoto b ON a.changerequestid = b.CHANGEREQUEST_ID WHERE a.statename = 'Mumbai'
      -- and  a.cityname = null
      AND a.createdon >= TO_DATE(P_FROMDATE,'DD-MM-YY') AND a.lastmodifiedon <= TO_DATE(P_TODATE,'DD-MM-YY');
    END;

So how to handle that condition, please suggest ?

Answer 1

Modify where clause to something like this:

   WHERE     (   a.statename = p_statename
              OR p_statename IS NULL)
         AND (   a.cityname = p_cityname
              OR p_cityname IS NULL)
         AND a.createdon >= NVL (p_fromdate, TRUNC (SYSDATE))
         AND a.lastmodifiedon <= NVL (p_todate, TRUNC (SYSDATE))

As of p_fromdate and p_todate: parameters are already dates, you shouldn't to_date them. nvl function's value is set to trunc(sysdate) because I don't know what value you'd want to have if users don't pass them - fix it, if necessary.