Return a result set in a SP from a received result set (cursor)

Question

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.

• Allocate
• Associate

Answer 1

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