CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle11g
Jacob
Jacob

Reputation: 14741

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.

Upvotes: 0

Views: 50

Answers (1)

Massimo Petrus
Massimo Petrus

Reputation: 1891

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;

Upvotes: 2

Related Questions