Sybase 12.4.2 manual 259, On Exception Resume Begin

CHAPTER 6 Using Procedures and Batches

CREATE PROCEDURE OuterProc()

ON EXCEPTION RESUME BEGIN

DECLARE res CHAR(5);

MESSAGE ’Hello from OuterProc.’; CALL InnerProc();

SELECT @res=SQLSTATE; IF @res=’52003’ THEN

MESSAGE ’SQLSTATE set to ’, res, ’ in OuterProc.’;

END IF END;

CREATE PROCEDURE InnerProc()

ON EXCEPTION RESUME

BEGIN

DECLARE column_not_found

EXCEPTION FOR SQLSTATE ’52003’;

MESSAGE ’Hello from InnerProc.’;

SIGNAL column_not_found;

MESSAGE ’SQLSTATE set to ’,

SQLSTATE, ’ in InnerProc.’;

END

The following statement executes the OuterProc procedure:

CALL OuterProc();

The message window of the server then displays the following:

Hello from OuterProc.

Hello from InnerProc.

SQLSTATE set to 52003 in OuterProc.

The execution path is as follows:

1OuterProc executes and calls InnerProc.

2In InnerProc, the SIGNAL statement signals an error.

3The MESSAGE statement is not an error-handling statement, so control is passed back to OuterProc and the message is not displayed.

4In OuterProc, the statement following the error assigns the SQLSTATE value to the variable named res. This is an error-handling statement, and so execution continues and the OuterProc message is displayed.

259