DECLARE HANDLER (Basic Syntax) | Teradata Vantage - DECLARE HANDLER (Basic Syntax) - Teradata Vantage - Analytics Database

SQL Stored Procedures and Embedded SQL
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
frc1628111662093.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
rjx1472253414573
lifecycle
latest
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