Sybase 12.4.2 Manual De Usuario

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:

1

OuterProc executes and calls InnerProc.

2

In InnerProc, the SIGNAL statement signals an error.

3

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

4

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