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

SQL Stored Procedures and Embedded SQL
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

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

ANSI Compliance

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

Required Privileges

None.

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 ;

Syntax Elements

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.

Usage Notes

You can specify one of the following in a handler declaration, but not both.
  • a list of SQLSTATE values and/or condition names
  • a list of generic conditions

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 Information