CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
user619804
user619804

Reputation: 2356

Oracle stored proc - dynamically generate select statement based on optional params

create or replace
PROCEDURE get_txn_by_account_id(
p_ACCOUNT_ID IN txn.ACCOUNT_ID%TYPE DEFAULT NULL
) 

IS

BEGIN
FOR x IN (SELECT * FROM txn where account_id=p_ACCOUNT_ID)
LOOP
    dbms_output.put_line(x.txn_ID || ', ' || x.txn_VER_NB);

END LOOP;
END get_txn_by_account_id;

my stored procedure issues a select based on one input as search criteria and outputs the search results. My input is ACCOUNT_ID. I'd like to add 4 additional input params (param_a, param_b, param_c, param_d) - at least two of which will be optional.

So I'll probably change my stored proc declaration like

PROCEDURE get_txn_by_account_id(
p_ACCOUNT_ID IN txn.ACCOUNT_ID%TYPE DEFAULT NULL,
p_PARAM_A IN txn.PARAM_A%TYPE DEFAULT NULL,
p_PARAM_B IN txn.PARAM_B%TYPE DEFAULT NULL,
p_PARAM_C IN txn.PARAM_C%TYPE DEFAULT NULL,
p_PARAM_D IN txn.PARAM_D%TYPE DEFAULT NULL
)

So my SELECT may have varying number of query params based on what optional params are passed to the stored proc.

For instance,

FOR x IN (SELECT * FROM txn where account_id=p_ACCOUNT_ID, param_a=p_PARAM_A, param_b=p_PARAM_B, param_c=p_PARAM_C, param_d=p_PARAM_D)
FOR x IN (SELECT * FROM txn where account_id=p_ACCOUNT_ID, param_a=p_PARAM_A, param_c=p_PARAM_C, param_d=p_PARAM_D)
FOR x IN (SELECT * FROM txn where account_id=p_ACCOUNT_ID, param_a=p_PARAM_A, param_b=p_PARAM_B, param_d=p_PARAM_D)

or

FOR x IN (SELECT * FROM txn where account_id=p_ACCOUNT_ID, param_a=p_PARAM_A, param_b=p_PARAM_B)

etc.

I'm having trouble with - how to code my stored proc so that the SELECT statement changes based on what optional params are passed to the stored proc - perhaps a string builder that builds a query statement based on optional params passed in to the proc, and then use the resulting string in the query? I'm not sure if this is even possible. Surely this is a common problem - anyone have any suggestions? Thanks!

Upvotes: 2

Views: 193

Answers (2)

user533832
user533832

Reputation: 

FOR x IN ( SELECT * 
           FROM txn 
           WHERE account_id=p_ACCOUNT_ID
             AND (p_PARAM_A is null or param_a=p_PARAM_A)
             AND (p_PARAM_B is null or param_b=p_PARAM_B)
             AND (p_PARAM_C is null or param_c=p_PARAM_C)
             AND (p_PARAM_D is null or param_d=p_PARAM_D) )

Upvotes: 3

Erkan Haspulat
Erkan Haspulat

Reputation: 12552

You can use the trick below:

select * from FOO
where
  COLUMN_1 = nvl(pParam1, COLUMN_1)
  and COLUMN_2 = nvl(pParam2, COLUMN_2)
  and ...

This ensures that if pParam1 is null, your condition always evaluates to true. And so on.

Upvotes: 1

Related Questions