Sybase 12.4.2 manual Nested compound statements and exception handlers, 263

CHAPTER 6 Using Procedures and Batches

Exception handling and atomic compound statements

•If the error handling code for the column not found exception is simply a RESIGNAL statement, control is passed back to the OuterProc procedure with SQLSTATE still set at the value 52003. This is just as if there were no error handling code in InnerProc. As there is no error handling code in OuterProc, the procedure fails.

When an exception is handled inside a compound statement, the compound statement completes without an active exception and the changes before the exception are not undone. This is true even for atomic compound statements. If an error occurs within an atomic compound statement and is explicitly handled, some but not all of the statements in the atomic compound statement are executed.

Nested compound statements and exception handlers

The code following a statement that causes an error is not executed unless an

ON EXCEPTION RESUME clause is included in a procedure definition.

You can use nested compound statements to give you more control over which statements are and are not executed following an error.

Drop the procedures Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial. You can do this by entering the following commands in the command window:

DROP PROCEDURE OUTERPROC;

DROP PROCEDURE INNERPROC

The following demonstration procedure illustrates how nested compound statements can be used to control flow. The procedure is based on that used as an example in “Default error handling in procedures” on page 256

CREATE PROCEDURE InnerProc()

BEGIN

DECLARE column_not_found

EXCEPTION FOR SQLSTATE VALUE ’52003’;

MESSAGE ’Hello from InnerProc’;

BEGIN

SIGNAL column_not_found;

MESSAGE ’Line following SIGNAL’

EXCEPTION

WHEN column_not_found THEN

MESSAGE ’Column not found handling’;

WHEN OTHERS THEN

RESIGNAL;

263