Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 276 / 536

Errors and warnings in procedures
256
Default error handling in procedures
This section describes how Adaptive Server IQ handles errors that occur during
a procedure execution, if you have no error handling built in to the procedure.
If you want to have different behavior from that described in this section, you
can use exception handlers, described in “Using exception handlers in
procedures” on page 261. Warnings are handled in a slightly different manner
from errors: for a description, see “Default handling of warnings in
procedures” on page 260
There are two ways of handling errors without using explicit error handling:
• Default error handling
The procedure fails and returns an error code
to the calling environment.
• ON EXCEPTION RESUME
If the ON EXCEPTION RESUME clause
is included in the CREATE PROCEDURE statement, the procedure
carries on executing after an error, resuming at the statement following the
one causing the error.
Default error handling Generally, if a SQL statement in a procedure fails, the procedure terminates
execution and control is returned to the application program with an
appropriate setting for the SQLSTATE and SQLCODE values. This is true
even if the error occurred in a procedure invoked directly or indirectly from the
first one.
The following demonstration procedures show what happens when an
application calls the procedure
OuterProc
, and
OuterProc
in turn calls the
procedure
InnerProc
, which then encounters an error.
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 ’SQLSTATE set to ’,
SQLSTATE, ’ in InnerProc.’;
END