15.00 - Return Codes - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Return Codes

SQL return codes provide information about the status of a completed executable SQL DML statement.

Status Variables for Receiving SQL Return Codes

ANSI/ISO SQL defines two status variables for receiving 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.

    Teradata Database defines a third status variable for receiving the number of rows affected by an SQL statement in a stored procedure:

  • ACTIVITY_COUNT
  • Teradata SQL defines a non-ANSI/ISO SQL Communications Area (SQLCA) that also has a field named SQLCODE for receiving return codes.

     

    For information on …

    See …

  • SQLSTATE
  • SQLCODE
  • ACTIVITY_COUNT
  • “Result Code Variables” in SQL Stored Procedures and Embedded SQL.

    SQLCA

    “SQL Communications Area (SQLCA)” in SQL Stored Procedures and Embedded SQL.

    Exception and Completion Conditions

    ANSI/ISO SQL defines two categories of conditions that issue return codes:

  • 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.

    For more information about exception conditions, see “Failure Response” on page 156 and “Error Response (ANSI Session Mode Only)” on page 155.

    For a complete list of exception condition codes, see Messages.

    Completion Conditions

    A completion condition indicates statement success.

    There are three categories of completion conditions:

  • 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

    Related Topics

    For more information, see:

  • “Statement Responses” on page 153
  • “Success Response” on page 154
  • “Warning Response” on page 155
  • 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.

    Teradata Database warning code.

    No data found or any other Exception

    SQLSTATE value corresponding to the error code.

    Teradata 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.