PL/SQL optional where

Question

Hey, I have this query in PL/SQL:

--Ver todos los atributos de las OL de una OS.
SELECT attr.swspattrdataid attr_data_id,
       att.swname attribute_swname,
       attr.swvalue attr_data_swvalue
  FROM sw_sp_attr_data attr, 
       sw_sp_attribute att
 WHERE swobjectid IN (SELECT swsporderlineid
                        FROM sw_sp_order_line
                       WHERE swsporderid = 21444963 --Orden 
                       **AND swsporderlineid = a_number**
                     );
   AND att.swspattributeid = attr.swspattributeid
 --AND att.swname LIKE '%%'                          --Filtrar por nombre

I need to have the AND filter between ** to be optional, so no matter if I put a number there the query runs OK, is this posible?

Thanks!

Answer 1

Declare the parameter with a default value of NULL. So in case it is not passed to the procedure it will automatically be null.

Then change the condition to:

AND (a_number IS NULL OR swsporderlineid = a_number)

Answer 2

You can replace the swsporderlineid = a_number clause with this OR clause:

   WHERE      swobjectid IN (SELECT swsporderlineid
                             FROM sw_sp_order_line
                             WHERE swsporderid = 21444963 --Orden 
                               AND (swsporderlineid = a_number OR a_number IS NULL));

Therefore, if a_number is null, the second line evaluates to true for all records, allowing the query to continue.