Consider the following stored procedure that returns a limit of four dynamic result sets to a calling embedded SQL application:
CREATE PROCEDURE TESTSP1(INOUT p1 INTEGER, OUT p2 INTEGER) DYNAMIC RESULT SETS 4 BEGIN DECLARE tab1_cursor CURSOR WITH RETURN ONLY TO CALLER FOR SELECT c1, c2, c3 FROM tab1; OPEN tab1_cursor; SET p1 = p1 + 1; SET p2 = 82; END;
To access the dynamic result set rows returned to the application by this procedure, declare a cursor like the following:
EXEC SQL BEGIN DECLARE SECTION; … long H1; long H2; long M1; long M2; long M3; … EXEC SQL END DECLARE SECTION; … EXEC SQL DECLARE TESTCUR CURSOR FOR 'CALL TESTSP1(:H1, :H2)'; EXEC SQL OPEN TESTCUR;
If the procedure returns one or more rows, the system sets SQLCODE to 3212 and SQLSTATE to ‘T3212’. This example does not use SQLSTATE return codes. The SQLSTATE codes equivalent to the SQLCODE values in the example are provided for the sake of completeness.
If the stored procedure returns no rows, the system sets SQLCODE to 0 and SQLSTATE to ‘T0000’:
if (SQLCODE == 3212) fetch_rows(); else if (SQLCODE != 0) error_check();
Fetching rows from a cursor declared for a stored procedure is similar to fetching rows from a cursor defined for an SQL request:
void fetch_rows() do { EXEC SQL FETCH TESTCUR INTO :M1, :M2, :M3; … } while (SQLCODE == 0);