Sybase 12.4.2 Manual De Usuario

CHAPTER 6    Using Procedures and Batches

The procedures both continued executing after the warning was generated, 
with SQLSTATE set by the warning (02000).

It is often desirable to intercept certain types of errors and handle them within 
a procedure, rather than pass the error back to the calling environment. This is 
done through the use of an exception handler.

An exception handler is defined with the EXCEPTION part of a compound 
statement (see “Using compound statements” on page 240). The exception 
handler is executed whenever an error occurs in the compound statement. 
Unlike errors, warnings do not cause exception handling code to be executed. 
Exception handling code is also executed if an error is encountered in a nested 
compound statement or in a procedure that has been invoked anywhere within 
the compound statement.

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 demonstration procedures used to illustrate exception handling are based 
on those used in “Default error handling in procedures” on page 256 In this 
case, additional code is added to handle the 

column not found

 error in the 

InnerProc

 procedure.

CREATE PROCEDURE OuterProc()

BEGIN

MESSAGE ’Hello from OuterProc.’;

CALL InnerProc();

MESSAGE ’SQLSTATE set to ’,

SQLSTATE,’ in OuterProc.’

END

CREATE PROCEDURE InnerProc()

BEGIN

DECLARE column_not_found

EXCEPTION FOR SQLSTATE ’52003’;

MESSAGE ’Hello from InnerProc.’;

SIGNAL column_not_found;

MESSAGE ’Line following SIGNAL.’;

EXCEPTION

WHEN column_not_found THEN