DECLARE HANDLER (Basic Syntax) | Teradata Vantage - DECLARE HANDLER (Basic Syntax) - Advanced SQL Engine - Teradata Database

Purpose

Associates a condition handler with one or more exception, completion, or user-defined conditions to be handled in a stored procedure.

Invocation

Executable control declaration.

Stored procedures only.

Syntax

DECLARE { CONTINUE | EXIT } HANDLER FOR
  { 
    { sqlstate_state_spec | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;

where:

sqlstate_state_spec

SQLSTATE [VALUE] sqlstate_code

CONTINUE

The type of handler action to be executed.

EXIT

The type of handler action to be executed.

sqlstate_code

The five-character literal SQLSTATE code to be handled.

You can specify any number of valid SQLSTATE values in a comma-separated list, but ‘00000’ which represents successful completion of statements, is not allowed.

SQLEXCEPTION

Generic condition to be handled.

You can specify one or any combination of the generic conditions in a comma-separated list.

SQLWARNING

Generic condition to be handled.

You can specify one or any combination of the generic conditions in a comma-separated list.

NOT FOUND

Generic condition to be handled.

You can specify one or any combination of the generic conditions in a comma-separated list.

condition_name

the name of the condition to be handled.

handler_action_statement

either a single statement or multiple statements enclosed in a compound statement that define the handler action.

The handler action is executed when a particular exception or completion condition is returned to the application, or when a user-defined condition is encountered.

The statement(s) can be any of the following:

• SQL DML, DDL, or DCL statements supported by stored procedures. These include dynamic SQL.
• Control statements, including nested compound statements.

Declaration (local variable, condition, cursor, or handler) statements are not allowed as a single statement for handler action. These can be submitted from within a compound statement.

ANSI Compliance

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

Authorization

None.

Usage Notes

You cannot repeat the same condition name, SQLSTATE code, or generic condition in handler declarations within the same compound statement.

You cannot specify both the condition name and the SQLSTATE value associated with the condition name in handler declarations within the same compound statement.

Related Topics