Sybase 12.4.2 manual Default error handling in procedures, Errors and warnings in procedures, 256

Errors and warnings in procedures

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

256