Optional Parameters

[Monkeygumbo]

Hi,

My procedure accepts one parameter, group_id. But its optional. If its left NULL, the program has to process all records. If it has a value, the program process all records with a group_id = the value.

I'm using a cursor that accepts the parameter currently...

CURSOR c_get_gl_rec1(p_group_id) IS
SELECT *
FROM table1
WHERE group_id = p_group_id;

but passing it a NULL won't select all records. Any suggestions?

MG

 

[jimbopalmer]

CURSOR c_get_gl_rec1(p_group_id) IS
SELECT *
FROM table1
WHERE group_id like nvl(p_group_id, '%');

may do want you want


I tried to remain child-like, all I acheived was childish.

 

[Monkeygumbo]

Will that also select records with a group_id that is NULL?

 

[jimbopalmer]

Oops I assumed p_group_id was the parameter, and so wrote it backwards

CURSOR c_get_gl_rec1(p_group_id) IS
SELECT *
FROM table1
WHERE p_group_id like nvl(group_id, '%');

I tried to remain child-like, all I acheived was childish.

 

[sem]

Cursor with LIKE will not retrieve records with empty group_id.


...WHERE (p_group_id is null or group_id = p_group_id)

Though in stored procedures I prefer to use 2 different cursors because had negative experience on efficiency of such constucts (full scan even with parameter passed) :-(

Regards, Dima

 

[Turkbear]

Hi,
Why not test the input parameter and use If statements to build your cursor sql code?

 

[Monkeygumbo]

Dynamically create the cursor structure? How do you do that?

 

[sem]

Use REF CURSOR.

Regards, Dima