Conditions Raised by a Handler Action | Teradata Vantage - Conditions Raised by a Handler Action - Advanced SQL Engine - Teradata Database

The action clause of a condition handler can be a nested or non-nested compound statement. Exception, completion or user-defined conditions raised in the action clause can be handled by a handler defined within the action clause.

If a condition raised by a handler action is not handled within the action clause, then that condition is not propagated outwards to search for suitable handlers. Other handlers associated with the compound statement cannot handle the condition raised by any handlers. Such conditions remain unhandled. The only exception is the RESIGNAL statement, whose condition is propagated outside the compound statement action clause in a handler.

The following table compares unhandled exception, completion, and user-defined conditions.

These situations are illustrated in the following cases and examples.

Case 1

Consider the following case of a handler action for an exception condition raising a new exception, which is then handled.

1. The stored procedure raises an exception with the SQLSTATE code ‘42000’, which means the referenced database object does not exist.
2. The exception condition is handled by a condition handler.
3. Then the handler action raises the divide by zero exception ‘22012’.
4. A handler exists within the handler action group of statements to handle this exception, and it is handled.

Case 2

Consider the following case of a handler action for an exception condition raising a new exception, which is then not handled.

1. The stored procedure raises an exception with the SQLSTATE code ‘42000’, which means the referenced database object does not exist.
2. Then the handler action clause raises the divide by zero exception ‘22012’.
3. If a suitable handler for this newly raised exception does not exist within the handler action group of statements, the newly raised condition is not propagated outside to search for handlers.
4. The handler action exits, and the original exception condition ‘42000’ is propagated outwards in search of a suitable condition handler.
5. If no suitable handler is found for the original condition, the stored procedure terminates and returns a an error code corresponding to the original exception condition ‘42000’.

Case 3

Consider the following case of a handler action for a completion condition raising an exception.

1. The stored procedure raises a completion condition (a warning) with the SQLSTATE code ‘T7473’, which means the requested sample size is larger than the table rows.
2. Then the handler action raises an exception condition ‘23505’ for attempting to insert a duplicate row in the table.
3. If a suitable handler for ‘23505’ exists within the handler action, the condition is handled.
4. If a suitable handler for ‘23505’ does not exist within the handler action, the original condition ‘T7473’ is propagated outward to look for a suitable handler to handle the condition.
5. If the original completion condition is handled and
   • if the handler is a CONTINUE type, the stored procedure execution continues with the statement that raised the completion condition.
   • if the handler is an EXIT type, control exits the compound statement that contains the EXIT handler.

     If the completion condition is not handled, the stored procedure execution continues with the next statement.

Case 4

Consider the following case of a handler action for a completion condition raising another completion condition.

1. The stored procedure raises a completion condition ‘T7473’, which means the requested sample size is larger than the table rows.
2. The completion condition is handled by a generic condition handler.
3. Then the handler action raises a “no data found” completion condition.
4. This new completion condition is ignored, and the stored procedure execution continues with the remaining statements.