ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 155
Constraints, Procedures, and Rules
Using Procedures
The syntax for the WHENEVER is as follows:
WHENEVER (SQLERROR
SQLWARNING
NOT FOUND}{STOP
CONTINUE
GOTO [:] Label
GO TO [:] Label }
The STOP option causes the current transaction to be rolled back, and the procedure's
execution is terminated. If an error occurs in evaluating the condition in an IF or WHILE
statement, or in evaluating the expression in a parameter or variable assignment
statement, the execution of the procedure terminates, and control is returned to the caller
with SQLCODE set to the last error encountered inside the procedure.
Within the procedure, the entire message buffer is not available. That is, SQLEXPLAIN
cannot be used. The built-in variable
::sqlcode holds only the error code from the first
message in the message buffer (guaranteed to be the most severe error).
In procedures, as elsewhere in ALLBASE/SQL, the message buffer is cleared out only
before executing an SQL statement. That is, execution of the following do
not cause the
message buffer to be reset:
• Assignment
•GOTO
•IF
• PRINT
• RETURN
• WHILE
The argument of any PRINT statement is passed back to the caller in the message buffer.
When the message buffer is reset, PRINT statements are not removed.
Runtime errors are accompanied by a generic error message indicating, by number, which
procedure statement caused the error. All SQL statements in a procedure and all non-SQL
statements except variable declarations, ENDIF, ELSE, ENDWHILE, END, and THEN, are
numbered consecutively from the beginning of the procedure. The following is an example
of a sequence of errors returned when an EXECUTE PROCEDURE statement fails:
Integer divide by zero. (DBERR 2601)
Error occurred executing procedure PURCHDB.DISCOUNT statement 2.(DBERR 2235)
Error occurred during evaluation of the condition in an IF or WHILE
statement or the expression in a parameter or variable assignment.
Procedure execution terminated. (DBERR 2238)
Using RAISE ERROR in Procedures
You can use the RAISE ERROR statement to generate an error within a procedure and make
a message available to users, as in the following example:
RAISE ERROR 7500 MESSAGE 'Error Condition';
RETURN 1;
The RAISE ERROR statement causes the message to be stored in the message buffer, and