SQLSTATE | Teradata Vantage - SQLSTATE - Advanced SQL Engine

SQLSTATE is a variable (declared explicitly as a host variable in embedded SQL applications and implicitly as a status variable in stored procedures) that receives SQL statement status information (error or warning code and the condition of an SQL statement, including control statements).

ANSI Compliance

SQLSTATE is ANSI/ISO SQL:2011-compliant. You should use it instead of SQLCODE for any new applications you develop.

The SQLSTATE status variable used by stored procedure programs is non-ANSI/ISO SQL:2011 standard.

Either SQLSTATE or SQLCODE must be declared for all embedded SQL applications written for ANSI session mode.

SQLSTATE is not valid for embedded SQL applications running in Teradata session mode.

Where SQLSTATE Receives Error Codes

CLI/TDP
Teradata Database
Preprocessor2 runtime manager

Structure of SQLSTATE

SQLSTATE is a five-character string value divided logically into a two-character class and a three-character subclass. SQLSTATE Class Definitions lists the ANSI/ISO SQL:2011-defined SQLSTATE classes.

Subclass values can be any numeric or simple uppercase Latin character string.

SQLSTATE Type Definition for Embedded SQL

Preprocessor2 requires the following SQLSTATE data type definitions for the indicated client application language.

Client Language	Data Type Definition
COBOL PL/I	CHARACTER(5)
C	CHARACTER(6) The sixth character is always a null terminator.

Declaring SQLSTATE

SQLSTATE declaration is different for embedded SQL and stored procedure applications:

SQLSTATE must be declared explicitly within an SQL DECLARE SECTION for embedded SQL applications.
SQLSTATE is declared implicitly within a stored procedure application.

Using Both SQLSTATE and SQLCODE

You can define both SQLSTATE and SQLCODE in the same embedded SQL compilation unit.

You can also test the values of both SQLSTATE and SQLCODE variables within the same stored procedure.

In either case, both structures contain valid result codes.

How Teradata Database Error Codes Map to SQLSTATE

Unless otherwise specified, CLI/TDP, preprocessor runtime, and Teradata Database error codes map into SQLSTATE values using the ANSI-defined option of implementation-defined classes.

Unmapped CLI/TDP errors are class T0.
Their subclass contains the 3-digit CLI error code.

For example, CLI error 157 (invalid Use_Presence_Bits option) produces class T0 and subclass 157.
Unmapped Teradata Database errors are class T1 through class T9.
The differentiating digit in the class number corresponds to the first digit of the Teradata Database error code.

The subclass contains the remaining 3-digit Teradata Database error code.

For example, error 3776 (unterminated comment) produces class T3 and subclass 776.

SQLCODE to SQLSTATE Exception Mapping

Some SQLCODE values are generated by errors not originating within CLI, TDP, or Teradata SQL.

SQLSTATE Usage Constraints in Stored Procedures

The following usages of SQLSTATE are valid within a stored procedure:

As the operand of a SET statement.
For example, the following statements are valid:
```
SET hErrorMessage = 'SQLSTATE' || sqlstate;
SET hSQLSTATE = SQLSTATE;
```
As an expression in an SQL statement within a stored procedure.
For example, the following statements are valid.
```
INSERT INTO table_1 (column_1)
VALUES (:SQLSTATE);
UPDATE table_1
SET column_1 = column_1 + :ACTIVITY_COUNT;
```

The following uses of SQLSTATE are not valid within a stored procedure.

SQLSTATE cannot be declared explicitly.
SQLSTATE cannot be SET to a value or an expression.
For example, the following statement is not valid.
```
SET SQLSTATE = h1 + h2;
```
SQLSTATE cannot be specified in the INTO clause of a SELECT statement.
For example, the following statement is not valid.
```
SELECT column_1 INTO :SQLSTATE FROM table_1;
```
SQLSTATE cannot be specified in place of the INOUT and OUT parameters of a CALL statement.

SQLSTATE | Teradata Vantage - SQLSTATE - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL