Teradata Stored Procedures & Error Handling: A Guide for Effective Programming

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.

Developers 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;

Each stored procedure can have exactly:

• 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

Upon completion of the exit handler, the standard code execution resumes within the encompassing block (specified by BEGIN…END) that triggered the exception. However, if there is no surrounding block, the stored procedure will terminate, although this is an uncommon scenario. It is important to note that the “exit handler” label can be deceiving and does not indicate the conclusion of the stored procedure. Rather, only the block which invoked the handler is terminated, allowing the code execution to proceed to the adjacent block.

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.

Upon executing a continue handler, code execution remains in the data block where the error occurred. However, in the case of an exit handler event, Teradata will terminate the code block associated with the error-triggering statement, allowing code execution to continue with the outer block, if applicable.

If an error occurs in a handler, its execution is halted, and the stored procedure searches for another handler by scanning data blocks from the innermost to the outermost. It’s important to note that an error event within the handler won’t resolve the handler error but rather the original error, which can cause the handler to execute again.

The following presents an illustration of nested handlers, wherein we nested three handlers to address the division by zero condition. We intentionally induced another division by zero exception (SET x=1/0) in two of the three handlers. As a result, the outermost handler ultimately addresses this error.

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);