user75ponic user75ponic - 4 years ago 73
SQL Question

Procedure - Dynamic where conditions

I have a procedure, based on the parameters, the where condition would differ.
The OUT parameters have to be INTO clause so that I can return the columns from the procedure.

Rather than having SQL condition for each if condition, what is the efficient way of doing this.

Answer Source

It looks to me more like a design question. So it depends on what you need to achieve and how you want to organize your code.

Possibilities

1- your "if" chain of queries in the same procedure

2- one procedure for each query

3 - if the differencies between the "where" parts are not so big use sql constructs, including unions, case , and\or etc to let coexist different cases in one query

4- build sql dynamically and use execute immediate

Usually i don't like the 1, i would try with 3 or 4, then proceed with 2 if i can't.

EDIT

With dynamic sql, for getting out results you can do

   EXECUTE IMMEDIATE stmt into o_total_count,o_total_sum,o_hold_status,o_normal_status;

In case you have input params for the query, you have to mark them with : and then add the USING clause with the appropriate input param.

Example

       EXECUTE IMMEDIATE 'select count(*) from departments where department_id=:id' INTO l_cnt USING l_dept_id; 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download