Sybase 12.4.2 manual 262

Errors and warnings in procedures

MESSAGE ’Column not found handling.’;

WHEN OTHERS THEN

RESIGNAL ;

END

The EXCEPTION statement declares the exception handler itself. The lines following the EXCEPTION statement are not executed unless an error occurs. Each WHEN clause specifies an exception name (declared with a DECLARE statement) and the statement or statements to be executed in the event of that exception. The WHEN OTHERS THEN clause specifies the statement(s) to be executed when the exception that occurred is not in the preceding WHEN clauses.

In this example, the statement RESIGNAL passes the exception on to a higher- level exception handler. RESIGNAL is the default action if WHEN OTHERS THEN is not specified in an exception handler.

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.
	Column not found handling.
	SQLSTATE set to 00000 in OuterProc.
Notes	• The lines following the SIGNAL statement in InnerProc are not executed;
	instead, the EXCEPTION statements are executed.
	• As the error encountered was a column not found error, the MESSAGE
	statement included to handle the error is executed, and SQLSTATE is reset
	to zero (indicating no errors).
	• After the exception handling code is executed, control is passed back to
	OuterProc, which proceeds as if no error was encountered.
	• You should not use ON EXCEPTION RESUME together with explicit
	exception handlers. The exception handler code is not executed if ON
	EXCEPTION RESUME is included.
	• You should use explicit exception handling code after each statement that
	may potentially generate an exception whenever you use ON
	EXCEPTION RESUME. You gain flexibility in handling errors, but the
	cost is more code and a higher risk of bugs in your code.

262