SQLCODE Function (SPL)
The SQLCODE function takes no arguments, but returns to its calling context the value of sqlca.sqlcode for the most recently executed SQL statement (whether static or dynamic) that the current SPL routine has executed. Only use SQLCODE in the context of a cursor.
You can use SQLCODE in expressions within SPL routines to identify the state of a dynamic cursor. This built-in function is useful in error handling and in contexts such as determining whether a query or function call has returned no rows, or when a cursor has reached the last row of the active set, or to identify other conditions when SPL program control should exit from a loop.
The following SPL program fragment illustrates the use of SQLCODE to detect the end of the active set of a cursor within a WHILE loop.
CREATE PROCEDURE ...
...
DEFINE myc1 ...
...
PREPARE p FOR "SELECT c1 FROM t1";
DECLARE cur FROM s;
OPEN cur;
FETCH cur INTO myc1;
WHILE (SQLCODE != 100)
FETCH cur INTO myc1;
-- process myc1
...
END WHILE;
END PROCEDURE;
The SQLCODE function is not needed in UDRs written in ESQL/C, which have direct access to the SQL Communications Area (SQLCA) through the GET DIAGNOSTICS statement of Dynamic SQL and by other mechanisms. The database server issues an error if the calling context of the built-in SQLCODE function is not an SPL routine.