Re: sql/plsql query question

roger wrote:

>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
>
>

It seems strange to say this but I agree with nobody.

Use native dynamic SQL and be sure to utilize the USING CLAUSE and bind variables
or you will make a horrible mess.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)