16.20 - DECLARE HANDLER (Basic Syntax) - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Stored Procedures and Embedded SQL

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1148-162K

Purpose

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

Invocation

Executable control declaration.

Stored procedures only.

Syntax



where:

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 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 Topics

For more information about: