1

I would like to know if it is possible to re-return a result set (opened cursor) in a stored procedure, that I received from a previous stored procedure.

Let's suppose

 CREATE OR REPLACE PROCEDURE SP2 ()
   RESULT SET 1
 P_SP2: BEGIN
  DECLARE SENTENCE VARCHAR(128);
  DECLARE STMT STATEMENT;
  DECLARE CUR2 CURSOR
    WITH RETURN TO CALLER
    FOR RS;
  SET SENTENCE = 'SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1';
  PREPARE RS FROM SENTENCE;
  OPEN CUR2;
 END P_SP2@

 CREATE OR REPLACE PROCEDURE SP1 ()
   RESULT SET 1
 P_SP1: BEGIN
  DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
  CALL SP2();
  ASSOCIATE RESULT SET LOCATORS (LOC1) WITH PROCEDURE SP2;
  ALLOCATE CUR1 CURSOR FOR RESULT SET LOC1;

  -- >>>>
  OPEN CUR1;
  -- <<<<

 END P_SP1 @

I do not know how to re-return the received result set. I know the cursor is already open, however, it is not returned when calling sp1.

These are internal sp calls, so I cannot change the first cursor to return to client.

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • It seems that I should use a temporary table, insert the values from the cursor on it, and open a new cursor from temp table. Other options? – AngocA Jun 23 '14 at 22:37

1 Answers1

0

when declare cursor there is either "WITH RETURN TO CLIENT" or ""WITH RETURN TO CALLER", you may choose to use "WITH RETURN TO CLIENT"

user10099560
  • 1