This section describes how SQL Anywhere handles errors that occur during a procedure execution, if you have no error handling built in to the procedure.
For different behavior, you can use exception handlers, described in Using exception handlers in procedures and triggers. Warnings are handled in a slightly different manner from errors: for a description, see Default handling of warnings in procedures and triggers.
There are two ways of handling errors without using explicit error handling:
Default error handling The procedure or trigger fails and returns an error code to the calling environment.
ON EXCEPTION RESUME If the ON EXCEPTION RESUME clause appears in the CREATE PROCEDURE statement, the procedure carries on executing after an error, resuming at the statement following the one causing the error.
The precise behavior for procedures that use ON EXCEPTION RESUME is dictated by the on_tsql_error option setting. For more information, see on_tsql_error option [compatibility].
Generally, if a SQL statement in a procedure or trigger fails, the procedure or trigger terminates execution and control returns 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 or trigger invoked directly or indirectly from the first one. In the case of a trigger, the operation causing the trigger is also undone and the error is returned to the application.
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.' TO CLIENT; CALL InnerProc(); MESSAGE 'SQLSTATE set to ', SQLSTATE,' in OuterProc.' TO CLIENT END; CREATE PROCEDURE InnerProc() BEGIN DECLARE column_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE 'Hello from InnerProc.' TO CLIENT; SIGNAL column_not_found; MESSAGE 'SQLSTATE set to ', SQLSTATE, ' in InnerProc.' TO CLIENT; END;
The DECLARE statement in InnerProc declares a symbolic name for one of the predefined SQLSTATE values associated with error conditions already known to the server.
The MESSAGE statement sends a message to the Interactive SQL Messages tab.
The SIGNAL statement generates an error condition from within the InnerProc procedure.
The following statement executes the OuterProc procedure:
The Interactive SQL Messages tab displays the following:
Hello from OuterProc.
Hello from InnerProc.
None of the statements following the SIGNAL statement in InnerProc execute: InnerProc immediately passes control back to the calling environment, which in this case is the procedure OuterProc. None of the statements following the CALL statement in OuterProc execute. The error condition returns to the calling environment to be handled there. For example, Interactive SQL handles the error by displaying a message window describing the error.
The TRACEBACK function provides a list of the statements that were executing when the error occurred. You can use the TRACEBACK function from Interactive SQL by entering the following statement: