Result code variables in an SQL statement, other than a control statement, must be prefixed with a colon character (:) when used in a stored procedure.
Initial Values of Result Code Variables
Result code variables are mapped to the database error codes and reflect the status of execution of stored procedure SQL statements, including control statements.
The initial value indicated in the last column is the value set at the beginning of stored procedure or embedded SQL application execution.
| Result Code Variable | Data Type | Initial Value |
|---|---|---|
| SQLCODE | SMALLINT | 0 |
| SQLSTATE | CHARACTER(5) CHARACTER SET is numeric or uppercase LATIN characters or a mix of both. |
’00000’ |
| ACTIVITY_COUNT | DECIMAL(18,0) | 0 |
The values set at the end of the statement execution reflect the exception condition or completion condition, if one occurs. These conditions, other than successful completion, can be handled if a condition handler is specified for the SQLSTATE value.
After successful completion, the result code variables are set to appropriate values for SQL statements other than control statements within the stored procedure. The result code variables do not change for control statements.
Restrictions on Result Code Variables in Stored Procedures
- The result code variables are local to a stored procedure.
- The result code are not exported to the calling procedure in the case of nested stored procedures.
- You cannot explicitly declare result code variables.
- You cannot specify result code variables in the following circumstances:
- As the assignment target (LHS) of a SET statement
- In the INTO clause of an SQL SELECT INTO statement
- In place of INOUT and OUT arguments in an SQL CALL statement