SQL return codes provide information about the status of a completed executable SQL DML statement.
Status Variables for Receiving SQL Return Codes
- SQLSTATE
- SQLCODE
SQLCODE is not ANSI/ISO SQL-compliant. The ANSI/ISO SQL-92 standard explicitly deprecates SQLCODE, and the ANSI/ISO SQL-99 standard does not define SQLCODE. The ANSI/ISO SQL committee recommends that new applications use SQLSTATE in place of SQLCODE.
- ACTIVITY_COUNT
Teradata SQL defines a non-ANSI/ISO SQL Communications Area (SQLCA) that also has a field named SQLCODE for receiving return codes.
Exception and Completion Conditions
- Exception conditions
- Completion conditions
Exception Conditions
An exception condition indicates a statement failure.
A statement that raises an exception condition does nothing more than return that exception condition to the application.
There are as many exception condition return codes as there are specific exception conditions.
Completion Conditions
A completion condition indicates statement success.
- Successful completion
- Warnings
- No data found
A statement that raises a completion condition can take further action such as querying the database and returning results to the requesting application, updating the database, initiating an SQL transaction, and so on.
Completion Condition | SQLSTATE Return Code | SQLCODE Return Code |
---|---|---|
Success | '00000' | 0 |
Warning | '01901' | 901 |
'01800' to '01841' | 901 | |
'01004' | 902 | |
No data found | '02000' | 100 |
Return Codes for Stored Procedures
The return code values are different in the case of SQL control statements in stored procedures.
The return codes for stored procedures appear in the following table.
Completion Condition | SQLSTATE Return Code | SQLCODE Return Code |
---|---|---|
Successful completion | '00000' | 0 |
Warning | SQLSTATE value corresponding to the warning code. | Database warning code. |
No data found or any other Exception | SQLSTATE value corresponding to the error code. | Database error code. |
How an Application Uses SQL Return Codes
An application program or stored procedure tests the status of a completed executable SQL statement to determine its status.
Condition | Action |
---|---|
Successful completion | None. |
Warning | The statement execution continues. If a warning condition handler is defined in the application, the handler executes. |
No data found or any other exception | Whatever appropriate action is required by the exception. If an EXIT handler is defined for the exception, the statement execution terminates. If a CONTINUE handler is defined, execution continues after the remedial action. |
Related Topics
- Exception conditions, see Failure Response and Error Response (ANSI Session Mode Only).
- Completion conditions, see Statement Responses, Success Response and Warning Response.
- SQLSTATE, SQLCODE, or ACTIVITY_COUNT, see “Result Code Variables” in Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .
- SQLCA, see “SQL Communications Area (SQLCA)” in Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .