Hi,

I am using DB2 UDB V7.2.5 for Windows. I try to call a stored procedure "procB" twice in a stored procedure "procA". However, only the first call of the stored procedure "procB" can return the result.

Please advise how can I get the result return from both call of "procB".

The following is the sample code for my case.
=====================================
CREATE PROCEDURE procA()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN

DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_var VARCHAR(30);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND

SET l_sqlcode = SQLCODE;

set temp_var = 'AAAAAAAAAAAAAAAAAAA';
call procB(temp_var);

set temp_var = 'BBBBBBBBBBBBBBBBBBB';
call procB(temp_var);

END!


CREATE PROCEDURE procB(IN v_msg VARCHAR(30))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;

DECLARE DMSG varchar(30);

DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT DMSG
FROM table (VALUES 1) as temptable ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;

VALUES (v_msg) INTO DMSG;

OPEN temp_cursor;

END!

=====================================
I suppose the result will be as follows.

D:\>db2 call procA()

1
AAAAAAAAAAAAAAAAAAA

1
BBBBBBBBBBBBBBBBBBB

"PROCA" RETURN_STATUS: "0"
=====================================
Thanks,
Vincent