How to return a empty cursor from a stored procedure?

Question

I have OUT parameter of a stored procedure as a REF CURSOR. Based on a particular condition, I would want to either return a result set (already implemented).

But how do I return an empty cursor when the condition fails? Without raising an exception? Just pasting pseudo code:

IF condition = true THEN
   OPEN OUT_CUR FOR 
   Select Some query

ELSE

   Return empty OUT_CUR

END IF

score 18 · Answer 1 · edited Nov 01 '16 at 11:56

18

you can try this

IF condition = true THEN
   OPEN OUT_CUR FOR 
   Select Some query;
ELSE
   OPEN OUT_CUR FOR 
       Select * from mtable where 1=2;
END IF
return OUT_CUR;

edited Nov 01 '16 at 11:56

Thor

498
1
6
15

answered Oct 09 '12 at 04:33

Satya

8,693
5
34
55

any other standard way to return empty set? – KeenUser Oct 09 '12 at 04:47

score 1 · Answer 2 · edited May 28 '20 at 22:34

1

IF condition = true THEN
   OPEN OUT_CUR FOR 
   Select Some query

ELSE

    OPEN OUT_CUR FOR select * from unnest(array[1,2]) arr where  false 

END IF

edited May 28 '20 at 22:34

Jared Forth

1,577
6
17
32

answered May 28 '20 at 15:32

Vusal Hasanli

11
1

1

So far as I can tell, the only difference between this and @Satya's proposal from eight years ago is that you use `unnest(array[1,2]) arr where false` whereas Satya uses `mtable where 1=2`. Can you edit your answer to discuss why your approach might be preferable to Satya's well-established answer? – Jeremy Caney May 28 '20 at 18:49
1

because no need any table for return empty cursor. – Vusal Hasanli Jun 21 '20 at 10:35

How to return a empty cursor from a stored procedure?

2 Answers2