Introduction to Teradata Stored Procedures & Error Handling

Error handlers in Teradata provide similar functionality to modern procedural languages (C++, Java) and are event-driven.

Without the availability of state-based error handling, one would need to implement and invoke code for each error and at each code location where the error could occur.

Not only is this a tremendous effort from a development point of view, but we can easily forget about some error handling.

Below you can see one example of such a programmatic approach, 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);

When using error handlers, the handler code for a specific error is defined once within the handler and executed automatically each time the error condition is raised.

Below you see the division by zero problems solved with an error handler. The error code ‘22012’ is returned in case of 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);

It is possible to define error handlers as responsible for all errors or warnings (generic handlers). Teradata will execute them if a particular error handler does not already cover an error/warning.

The developer does not need to know all possible kinds of errors and warnings in advance.

A generic handler is used to clean up before exiting a stored procedure. See below two examples for generic exception and warning handlers:

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.

Within a stored procedure, all executed SQL statements return a status: completed, warning, or exception. An error handler can handle exceptions and warnings.

The status of each executed SQL statement is returned in the read-only variable SQLSTATE. This is implicitly available in each Stored Procedure usually used within generic error handlers to find further details about an error or warning.

There exists another read-only variable called SQLCODE, but it is obsolete and should not be used anymore.

SQLSTATE consists of 5 characters. The first two characters define the error class. The remaining three characters represent the error.

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

There are two primary error handlers: Exit handlers and continue handlers.

The Exit Handler

Whenever the exit handler’s execution is finished, the standard code execution continues with the block (defined by BEGIN…END), which surrounds the statement which caused the exception. The stored procedure terminates if there is no surrounding block, but this is an exception. Don’t get fooled by the name “exit handler,” assuming the stored procedure will end. Only the block causing the handler execution is terminated, and the code execution is passed to the surrounding block.

The Continue Handler

The continue handler continues with the following statement after the one which triggered the handler execution.

Raising of conditions

Usually, the following events raise conditions that will trigger 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

We can declare handlers in each data block (BEGIN…END). Different data blocks can handle various errors or even the same errors. In the case of an error, the code blocks are searched for an appropriate handler, starting with the block where the error occurred until the outermost BEGIN…END is reached. If none of the code blocks contains a handler, the stored procedure will exit with an error.

After executing a continue handler, code execution persists in the data block where the error is triggered. In an exit handler event, Teradata will terminate the error triggering statement’s code block, and code execution will continue with the outer block (if any).

Suppose an error is raised within a handler. In that case, the handler execution is terminated, and the stored procedure tries to find another handler by continually searching the data blocks from the innermost to the outermost. Be aware that an error event in the handler will not solve this handler error but the original error, leading to the handler execution!

Below is an example of nested handlers. We nested three handlers for the division by zero condition. Intentionally we cause another division by zero exception (SET x=1/0) in two of the three handlers. Therefore the outermost handler finally is the one who will handle 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);