How to handle optional parameters in SQL query?

Question

Hi, guys.

Say I have a sample table:

 id_pk  value
------------
 1       a
 2       b
 3       c

And I have a sample PL/SQL block, which has a query that currently selects a single row into an array:

declare

  type t_table is table of myTable%rowtype;

  n_RequiredId myTable.id_pk%type := 1;  
  t_Output t_table := t_table();

begin

  select m.id_pk, m.value
    bulk collect into t_Output
    from myTable m 
   where m.id_pk = n_RequiredId;

end;

What I need to do is to implement an ability to select a single row into an array, as shown in the block above, OR to select all rows into an array, if n_RequiredID, which is actually a user-input parameter, is set to null.

And, the question is, what's the best practice to handle such situation?

I can think of modifying where clause of my query to something like this:

where m.id_pk = nvl(n_RequiredId, m.id_pk);

but I suppose that's going to slow down the query if the parameter won't be null, and I remember Kyte said something really bad about this approach.

I can also think of implementing the following PL/SQL logic:

if n_RequiredId is null then 

  select m.id_pk, m.value bulk collect into t_Output from myTable m;

else

  select m.id_pk, m.value bulk collect
    into t_Output
    from myTable m
   where m.id_pk = n_RequiredId;

end if;

But would become too complex if I encounter more than one parameter of this kind.

What would you advice me?

Answer 1

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.

Answer 2

This article by Tom Kyte outlines an approach suggested by Bryn Llewellyn using dynamic SQL where redundant predicates are optimized away:

http://www.oracle.com/technetwork/oramag/2009/09-jul/o49asktom-090487.html

Regards, Rob.

Answer 3

OMG_Ponies' and Rob van Wijk's answers are entirely correct, this is just supplemental.

There's a nice trick to make it easy to use bind variables and still use dynamic SQL. If you put all of the binds in a with clause at the beginning, you can always bind the same set of variables, whether or not you're going to use them.

For instance, say you have three parameters, representing a date range and an ID. If you want to just search on the ID, you could put the query together like this:

with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
        on parameters.search_id = your_table.id;

On the other hand, if you need to search on the ID and date range, it could look like this:

with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
         on parameters.search_id = your_table.id
            and your_table.create_date between (parameters.start_date
                                                and parameters.end_date);

This may seem like an round-about way of handling this, but the end result is that no matter how you complicated your dynamic SQL gets, as long as it only needs those three parameters, the PL/SQL call is always something like:

execute immediate v_SQL using v_start_date, v_end_date, v_search_id;

In my experience it's better to make the SQL construction slightly more complicated in order to ensure that there's only one line where it actually gets executed.