Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 280 / 536

Errors and warnings in procedures
260
Default handling of warnings in procedures
Warnings are handled differently from errors. While the default action for
errors is to set a value for the SQLSTATE and SQLCODE variables, and return
control to the calling environment, 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 before
continuing with the tutorial. You can do this by entering the following
commands in the command window:
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” on page 256. 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 then displays the following:
Hello from OuterProc.
Hello from InnerProc.
SQLSTATE set to 02000 in InnerProc.
SQLSTATE set to 02000 in OuterProc.