DB2 Get result set from nested stored procedure

Question

I have a problem with db2. I'm new in this db. I am trying to get a result set from nested stored procedure but the result is "Query ok - 0 rows" :

Result of executed query

SP1 is:

 CREATE OR REPLACE PROCEDURE "SPSYSTEMMESSAGE" (IN pCode INTEGER) 
LANGUAGE SQL
SPECIFIC SQL210127173003073
DYNAMIC RESULT SETS 1

BEGIN
  DECLARE cursor1 CURSOR WITH RETURN TO CALLER FOR
  SELECT CODE, MESSAGE FROM SYSTEMMESSAGES WHERE CODE = pCode;
  OPEN cursor1;
 END

SP2 CALLER:

CREATE OR REPLACE PROCEDURE "SPADDINSTITUTION"
(IN param1 INTEGER)
LANGUAGE SQL


DYNAMIC RESULT SETS 1
 
BEGIN 

  IF (params IS NULL OR params = '') THEN 
       
               
    CALL SPSYSTEMMESSAGE(-1);  
  
  ELSE
        INSERT INTO TABLE VALUES (values...);
  END IF;
  
    
END

CALL SP2 : CALL SPADDINSTITUTION(''); RESULTS: OK. No rows were affected

I need to get the result set like this:

Result that I want

question from:https://stackoverflow.com/questions/65941265/db2-get-result-set-from-nested-stored-procedure

Answer 1

Your code cannot give the result you want, because your calling procedure (SPADDINSTITUTION) is failing to consume the result set returned by SPSYSTEMMESSAGE.

This is frequently asked, and you can find samples if you research.

Opening a cursor in the nested sproc is not enough, the calling sproc must consume the result-set.

Learn about these statements, all of which must appear in your calling procedure:

• associate result set locator ... with procedure ...

• allocate .. cursor for result set ...`

• fetch from .... into ... ( you must fetch each row in the result set until no more rows are returned from the nested procedure).

You have not explained how exactly the caller procedure should act on the result set, but that is a separate matter from consuming the result set.

You might want to pipeline the fetched result-set rows (one by one, with the PIPE() statement) to pass to the client program, we don't know, as you have not made it clear.