Default error handling in procedures

This section describes how Sybase 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, you can use exception handlers, described in “Using exception handlers in procedures”. Warnings are handled in a slightly different manner from errors: for a description, see “Default handling of warnings in procedures”.

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.

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 [TSQL]” in Chapter 2, “Database Options,” in Sybase IQ Reference Manual.

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

Notes

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 server window and the dbisql Messages pane.
The SIGNAL statement generates an error condition from within the InnerProc procedure.

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.

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. No statements following the CALL statement in OuterProc execute. The error condition is returned to the calling environment to be handled there. For example, dbisql handles the error by displaying a message window describing the error.