The examples demonstrate handler syntax. The division statement has been moved off the comment line so it actually executes. Procedures without output do not demonstrate the final value of x to the client; add explicit output assertions when testing application error handling.
Introduction to Teradata Stored Procedures & Error Handling
Teradata error handlers offer comparable functionality to contemporary procedural languages such as C++ and Java and operate on an event-driven basis.
Without state-based error handling, it’s necessary to implement and invoke code for every error and at every location where an error may occur.
This is a significant development endeavor, and it’s possible to overlook error handling.
Here is an example of a programmatic approach for handling division by zero errors.
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10; -- Error Handling programmatically
CASE WHEN n = 0 THEN SET x = 0;
ELSE SET x = (x/ n);
END CASE;
END;
CALL DIVISION_BY_ZERO(0);
Error handlers automatically execute a defined code for a specific error each time the error condition is raised.
The following shows the resolution of division by zero issues through an error handler. The error code ‘22012’ is utilized to signify a division by zero error.
REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
DECLARE EXIT HANDLER FOR SQLSTATE '22012' SET x = 0; -- Error Handler
SET x = (x/n);
END;
CALL DIVISION_BY_ZERO(0);
Error handlers, known as generic handlers, are responsible for all errors or warnings. Teradata will execute these handlers if a specific error handler does not already cover an error or warning.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookDevelopers do not require prior knowledge of every potential error or warning.
A generic handler cleans up before exiting a stored procedure. Below are two examples of handlers for exceptions and warnings:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x =0;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x =0;
Handler declarations are scoped to their compound BEGIN…END block. The same condition can have handlers in different nested blocks; resolve handler precedence and eligibility according to that scope.
• One generic handler for exceptions (SQLEXCEPTION)
• One generic handler for warnings (SQLWARNING)
• One generic handler in case a cursor does not return any data (NOTFOUND)
• Per code block (BEGIN…END) and error code, exactly one specific handler
We can handle the same error code in different code blocks.
SQL statements within a stored procedure return a completed, warning, or exception status. An error handler can handle exceptions and warnings.
The status of executed SQL statements can be found in the read-only variable SQLSTATE. This variable is typically utilized in Stored Procedures to gather additional information about an error or warning and is commonly accessed by generic error handlers.
Another read-only variable, SQLCODE, is considered obsolete and should not be utilized.
SQLSTATE is a 5-character code comprising a 2-character error class and a 3-character error code.
Many of the error codes returned by Teradata are mapped 1:1 to the SQLSTATE variable (but not all of them). For example, the Teradata error code 3776 is assigned to T3776 (Error class T3 = Teradata 3).
Two main error handlers exist: Exit handlers and continue handlers.
The Exit Handler
An EXIT handler leaves the compound block in which the handler is declared. That need not be the innermost block containing the failing statement. If the declaring block is the procedure’s outer block, the procedure exits.
The Continue Handler
The continue handler proceeds to the subsequent statement after the triggering statement.
Raising of conditions
Certain events typically initiate the conditions necessary to activate handler execution.
• SQL status exception or warning (after executing an SQL statement)
• The SIGNAL statement, which is used to raise an exception programmatically
• The RESIGNAL statement passed by a continue handler to the surrounding block (BEGIN…END)
Handler Propagation
Handlers can be declared within every data block (BEGIN … END). Multiple data blocks can handle distinct or identical errors. When an error occurs, the code blocks are scanned to locate the appropriate handler, beginning from the block where the error occurred and concluding at the outermost BEGIN…END. The stored procedure will terminate with an error if no code block includes a handler.
A CONTINUE handler resumes according to the handler’s documented continuation semantics. An EXIT handler terminates its declaring compound block. Use observable output when testing nested cases; a successful CALL alone does not establish which handler ran.
A handler is not a catch-all mechanism for its own failures. Conditions raised within a handler are subject to the enclosing scope and applicable handler rules. Avoid relying on a silent nested example as proof of a particular recovery path.
The following nested-handler example is intended to explore propagation of division-by-zero conditions. A successful CALL without returned state does not prove the described path; add observable assertions before using it as a recovery-pattern demonstration.
REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10.00;
DECLARE CONTINUE HANDLER FOR SQLSTATE '22012' SET x=0; -- handles the error
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '22012' SET x = 1/0;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '22012' SET x = 1/0;
SET x = x / n;
END;
END; END;
CALL DIVISION_BY_ZERO(0);
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
hello.. I am trying to get exception message which is coming out of it. Is there any way, I can get exact message and log it in ErrorLog table? Below is error message I am getting. My SQL is dynamic so there is no specific error I want to log in table.
Executed as Single statement. Failed [7547 : HY000] Target row updated by multiple source rows.
Elapsed time = 00:00:00.171
STATEMENT 1: Update Statement failed.