Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Multiple Field Search Stored Procedure Help

    Hi guys,
    I have written an Oracle 9i SP to search some tables in my database, the SP receives 4 parameters and returns a list of records based on received params, the requirements say that if any one of those received params is missing (i.e. = null or empty string), it will not be considered in the search. The SP should match all the non null params in its WHERE condition. Here is an explanation of the code that should be executed in case all parameters have values:

    procedure SearchDb(param1 varchar2(250), param2 varchar2(250), param3 varchar2(250), param4 varchar2(250), REF_CURSOR some_cursor)
    open some_cursor for
    select * from table_name t
    where t.col1 = param1
    and t.col2 = param2
    and t.col3 = param3
    and t.col4 = param4 ;
    end SearchDb;
    My question is, what is the best/shortest way to write such an SP? I belive that there is better way than what I did (writng if-then-else statments to check for non-null params and exclude them from search - this leaded to a large number of conditional statements). I thought of building the sql string dynamically and using EXECUTE IMMEDIATE, but I think it is not good performance wise. Please note that it is ANDing in the WHERE clause not ORing.

    Thanks for any help

  2. #2
    Join Date
    Nov 2002
    I often see:

    select * from table_name t
    where (param1 is null OR t.col1 = param1)
    and (param2 is null OR t.col2 = param2)
    and (param3 is null OR t.col3 = param3)
    and (param4 is null OR t.col4 = param4) ;

    I don't know if it's the best way, but it's short :-)

  3. #3
    Join Date
    Sep 2002
    Provided Answers: 1
    Ivon's solution is fine as long as the data volumes involved are not too large - because it will always involve a full scan of the table.

    For larger data volumes a dynamic query would be give much better performance, as it would allow the optimizer to choose the best access path for the set of parameter actually supplied. The important thing is to use bind variables for the parameter values like this:
    q := 'select * from table_name t where 1=1';
    if param1 is not null then
      q := q || ' and t.col1 = :p1';
      q := q || ' and :p1 is null';
    end if;
    open some_cursor for q using param1, param2, param3, param4;
    Note: the ELSE part of each IF is required because native dynamic SQL requires a fixed list of parameters that must match bind variables in the query. You could use the DBMS_SQL package to avoid that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts