sql - How to call stored procedure inside another stored procedure and set the values as in variable in DB2

Question

I need to call stored procedure into another stored procedure and return value stored in variable in db2.

Thanks

Answer 1

One examples could be this one:

 DECLARE STMT STATEMENT;
 PREPARE STMT FROM 'CALL MODIFY_DESCENDANTS (?, ?)';
 EXECUTE STMT USING LOG_ID, LEVEL;

Taken from log4db2 - https://github.com/angoca/log4db2/blob/master/src/main/sql-pl/03-UtilityBody.sql#L729

If the stored procedure returns a result set, and you want to take values from that, then you need to call the stored procedure and use the associate and allocate statements. For an example, you can visit https://www.toadworld.com/platforms/ibmdb2/w/wiki/7460.call-allocate-and-associate

CALL EMPR;
ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE EMPR;
ALLOCATE C1 CURSOR FOR RESULT SET LOC1;