I have a stored procedure that is supposed to return two result sets, but only one result set is coming back. I verified that both queries return valid data, but (for some reason) only the last result set is returned.
The procedure is actually a C-program (an "external" procedure) with embedded SQL.
Please take a look, and let me know what I'm doing wrong. I'm sure the answer is blindingly obvious, but I've been looking at this for so long that I've lost all objectivity.
Thank you very much!
CREATE PROCEDURE MyProc (
... some parameters ...
RESULT SETS 2
LANGUAGE C NOT DETERMINISTIC MODIFIES SQL DATA
EXTERNAL NAME MYPROC
PARAMETER STYLE GENERAL WITH NULLS
... some stuff ...
DECLARE resultset CURSOR FOR
DECLARE resultset2 CURSOR FOR
EXEC SQL OPEN resultset;
EXEC SQL OPEN resultset2;
EXEC SQL SET RESULT SETS CURSOR resultset;
EXEC SQL SET RESULT SETS CURSOR resultset2;