sql/plsql query question

From: roger <xrsr_at_rogerware.com>
Date: Tue, 04 Nov 2003 16:58:34 GMT
Message-ID: <Xns94295B328E850rsrrogerwarecom@216.148.227.77>

Please consider the following psuedo procedure:

  procedure foo (p_target_value int)
  is
  begin
    select bla from table1 t1
    where

     (p_target_value is null or 
       t1.some_column in  
         (select t2.some_column from some_other_table t2
            where some_other_column = p_target_value))

    order by whatever;
  end;

Consider that there could be several such parameters, each with a similar clause anded to the query.

My question is if this a useful/valid/correct/efficient approach to handling cases where there are various optional query parameters that may or may not be specified?

i.e. is SQL smart enough to detect that there is no need to do the sub select on t2 based on the runtime value of [tthe PL/SQL variable?

thanks Received on Tue Nov 04 2003 - 10:58:34 CST