DECLARE CONDITION | Teradata Vantage - DECLARE CONDITION - Advanced SQL Engine

Assign a mnemonic name to an SQLSTATE code, or declare a user-defined condition.

ANSI Compliance

DECLARE CONDITION is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable control declaration.

Stored procedures only.

Syntax

DECLARE condition_name CONDITION
  [ FOR SQLSTATE [VALUE] sqlstate_code ] ;

Syntax Elements

condition_name: The mnemonic name to be associated with an SQLSTATE code. If you do not specify an SQLSTATE value, the condition name is used to declare a user-defined condition.
sqlstate_code: The five-character literal SQLSTATE code to be handled.; You cannot specify ‘00000’ which represents successful completion of statements.

Usage Notes

The DECLARE CONDITION statement allows you to declare a symbolic name for a condition and optionally associate it with a particular SQLSTATE value. This makes it easier to remember what condition the SQLSTATE value represents.

If the conditions represented by the available SQLSTATE values do not meet your needs, you can also define custom conditions that are specific to your stored procedure.

To declare a user-defined condition, simply declare a condition name without associating it with any SQLSTATE value in the condition declaration. The database will treat condition_name as a user-defined condition in this case. You can use the SIGNAL statement with the condition name to explicitly raise the user-defined condition.

The following rules apply to condition declarations:

A condition name must be a valid identifier. The name can be the same as that of a local variable, parameter, FOR loop alias/column, or label name declared in the stored procedure.
You can optionally associate a condition name with an SQLSTATE value. For example, the condition name divide_by_zero can be associated with the SQLSTATE value '22012'.
You can declare the same condition name in different nested or non-nested compound statements. You can associate that condition name with the same SQLSTATE value or with a different SQLSTATE value in each of the compound statements. The scoping rules of compound statements allow each declaration to define a different condition.
You cannot declare the same condition name more than once in the same compound statement. Otherwise, error SPL1080 is reported during stored procedure compilation, and the stored procedure is not created.
You must declare condition and variable declarations before any other type of declaration in a compound statement.
You cannot declare more than one condition name to be associated with the same SQLSTATE value in the same compound statement. Otherwise, error SPL1081 is reported during stored procedure compilation, and the stored procedure is not created.
A handler defined for an SQLSTATE value can also handle any explicitly declared condition associated with that SQLSTATE value.

Example: Using the Same Name for Local Variables, Conditions, and Aliases

The following example illustrates the usage of the same name for local variables, conditions, and aliases as follows:

divide_by_zero is used as a variable and a condition name
IOParam1 is used as a parameter and a condition name

cs1 is used as a label name and a condition name

CREATE PROCEDURE dec1(INOUT IOParam1 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero INTEGER;
   DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
   DECLARE IOParam1 CONDITION;
   DECLARE cs1 CONDITION;
   DECLARE alias1 CONDITION;
   FOR rp1 AS c_rp1
      CURSOR FOR
         SELECT c1 AS alias1 FROM tab1
   DO
         SET IOParam1 = rp1.alias1;
   END FOR;
   ...
END cs1;

Example: Using Condition Names in Nested Compound Statements

The following example illustrates the usage of condition names in nested compound statements. The condition declarations define the same condition name, divide_by_zero, but the declarations are made within different compound statements, and they define the same or different conditions in each compound statement.

CREATE PROCEDURE dec2()
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
   cs2: BEGIN
      DECLARE divide_by_zero CONDITION;
      cs3: BEGIN
         DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
         ...
      END cs3;
      cs4: BEGIN
         DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '42000';
         ...
      END cs4;
      ...
   END cs2;
   ...
END cs1;

Example: Condition Names in a Compound Statement

The following example illustrates the scope of a condition name in a compound statement.

During stored procedure execution, the scope of condition name exception_cond declared in line 3 is compound statements cs1 and cs2. The scope of exception_cond declared in line 7 is cs2. The definition of exception_cond in cs2 overwrites the definition of exception_cond in cs1.

The INSERT statement in line 10 raises exception ERRTEQTVNOEXIST (SQLCODE 3807 and SQLSTATE '42000'). Since a CONTINUE handler was defined to handle exception_cond, which is associated with SQLSTATE '42000' in cs2, this handler (declared in line 8) is invoked.

The INSERT statement in line 13 also raises exception ERRTEQTVNOEXIST. However, since there is no handler declared to handle SQLSTATE '42000' in cs1 (the containing outer compound statement), the stored procedure terminates with exception ERRTEQTVNOEXIST (SQLCODE 3807 and SQLSTATE '42000').

1. CREATE PROCEDURE dec3()
2. cs1: BEGIN
3.    DECLARE exception_cond CONDITION FOR SQLSTATE VALUE '22012';
4.    DECLARE CONTINUE HANDLER FOR exception_cond
5.    ...
6.    cs2: BEGIN
7.       DECLARE exception_cond CONDITION FOR SQLSTATE VALUE '42000';
8.       DECLARE CONTINUE HANDLER FOR exception_cond
9.       ...
10.      INSERT INTO Tab1 VALUES (10); -- Raises exception '42000'
11.      ...
12.   END cs2;
13.   INSERT INTO Tab1 VALUES (10);  -- Unhandled exception '42000'
14.   ...
15. END cs1;

Example: Using Different Condition Names in a Compound Statement

The following example illustrates the usage of different condition names in a compound statement. The condition names divide_by_zero and balance_too_low are both declared within the same compound statement.

CREATE PROCEDURE dec4()
cs1: BEGIN
   DECLARE  divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
   DECLARE  balance_too_low CONDITION;
   ...
END cs1;

Example: Exceptions for an SQLSTATE Value

The following example illustrates that a handler defined to handle exceptions related to an SQLSTATE value can also handle explicit conditions associated with that SQLSTATE value. During stored procedure execution, the CONTINUE handler can handle the divide-by-zero condition and any exception with SQLSTATE '22012'.

CREATE PROCEDURE dec5()
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
   ...
   SET IOPar1 = 10/0;
   ...
   SIGNAL divide_by_zero;
   ...
END cs1;

Example: Results When Condition Names Are Declared Twice Within the Compound Statement

You cannot use the same condition name more than once in the same compound statement. In the following example, the condition name divide_by_zerois declared twice within the compound statement cs1. During stored procedure compilation, error SPL1080 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE dec6()
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '22012';
   DECLARE divide_by_zero CONDITION FOR SQLSTATE VALUE '42000';
   ...
END cs1;

Example: Results When the Same SQLSTATE Value Is Associated with Different Condition Names

You cannot associate the same SQLSTATE value with different condition names in the same compound statement. In the following example, the SQLSTATE value ‘22012’ is associated with both divide_by_zero and zero_division condition names in the same compound statement. During stored procedure compilation, error SPL1081 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE dec7()
BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE zero_division CONDITION FOR SQLSTATE '22012';
   ...
END;

Related Information

SQLSTATE codes and their meanings, see SQLSTATE Mappings.
SIGNAL statement, see SIGNAL Condition name must be a valid identifier, see Example: Using the Same Name for Local Variables, Conditions, and Aliases.
Associate a condition name with an SQLSTATE value, see Example: Using the Same Name for Local Variables, Conditions, and Aliases.
Scoping rules of compound statements that allow each declaration to define a different condition, see Example: Using Condition Names in Nested Compound Statements and Example: Condition Names in a Compound Statement.
Declaring the same condition name more than once in the same compound statement, see Example: Results When Condition Names Are Declared Twice Within the Compound Statement
Declaring the condition and variable declarations before any other type of declaration in a compound statement, see Example: Using the Same Name for Local Variables, Conditions, and Aliases.
Declaring more than one condition name to be associated with the same SQLSTATE value in the same compound statement, see Example: Results When the Same SQLSTATE Value Is Associated with Different Condition Names.
A handler defined for an SQLSTATE value that also handles any explicitly declared condition associated with that SQLSTATE value, see Example: Exceptions for an SQLSTATE Value.

DECLARE CONDITION | Teradata Vantage - DECLARE CONDITION - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL