Default handling of warnings in procedures

Errors and warnings are handled differently. While the default action for errors is to set a value for the SQLSTATE and SQLCODE variables, and return control to the calling environment in the event of an error, the default action for warnings is to set the SQLSTATE and SQLCODE values and continue execution of the procedure.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures by entering the following commands in the command window before continuing with the tutorial:

DROP PROCEDURE OUTERPROC;
DROP PROCEDURE INNERPROC

The following demonstration procedures illustrate default handling of warnings. These demonstration procedures are based on those used in “Default error handling in procedures”. In this case, the SIGNAL statement generates a row not found condition, which is a warning rather than 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 row_not_found
		EXCEPTION FOR SQLSTATE '02000';
	MESSAGE 'Hello from InnerProc.';
	SIGNAL row_not_found;
	MESSAGE 'SQLSTATE set to ',
	SQLSTATE, ' in InnerProc.';
END

The following statement executes the OuterProc procedure:

CALL OuterProc();

The message window of the server or the dbisql Messages pane then displays the following:

Hello from OuterProc.

Hello from InnerProc.

SQLSTATE set to 02000 in InnerProc.

SQLSTATE set to 00000 in OuterProc.

The procedures both continued executing after the warning was generated, with SQLSTATE set by the warning (02000).

Execution of the second MESSAGE statement in InnerProc resets the warning. Successful execution of any SQL statement resets SQLSTATE to 00000 and SQLCODE to 0. If a procedure needs to save the error status, it must do an assignment of the value immediately after execution of the statement which caused the error warning.