Creating Query to fetch result set with optional input parameters

Recently I faced a issue of creating a query to fetch result set which included use of optional parameters. Which really means is that the result set should give output even when no optional parameter is passed.

The issue can be resolved by use of Flags.

Take an Instance we have two optional parameters. So we will need two flags

DECLARE FLAG_A CHAR(1) DEFAULT 'N';    

DECLARE FLAG_B CHAR(1) DEFAULT 'N';

IF INPUT_OPTIONAL_PARAMETER_1 <> '' THEN    

   SET FLAG_A = 'Y';            

 ELSE                            

   SET FLAG_A = 'N';            

 END IF;          

               

 IF INPUT_OPTIONAL_PARAMETER_2 <> ''    THEN    

   SET FLAG_B = 'Y';             

 ELSE                            

   SET FLAG_B = 'N';             

 END IF;                            

SELECT * FROM TABLE WHERE 

TO_DATE(CHAR(REQUESTED_DATE),'MMDDYYYY') 

BETWEEN 

TO_DATE(CHAR(FROM_DATE),'MMDDYYYY') 

AND       

TO_DATE(CHAR(TO_DATE),'MMDDYYYY')           

AND REQUIRED_COLUMN = INPUT_REQUIRED_PARAMETER                        

AND (OPTIONAL_COLUMN_1 = INPUT_OPTIONAL_PARAMETER_1 OR FLAG_A = 'N')

AND (OPTIONAL_COLUMN_2 = INPUT_OPTIONAL_PARAMETER_2 OR FLAG_B = 'N'));