Using SUSPEND, EXIT, and END With Procedures
Go Up to The Procedure Body
SUSPEND statement suspends execution of a select procedure, passes control back to the program, and resumes execution from the next statement when the next
FETCH is executed.
SUSPEND also returns values in the output parameters of a stored procedure.
SUSPEND should not be used in executable procedures, since the statements that follow it will never execute. Use
EXIT instead to indicate to the reader explicitly that the statement terminates the procedure.
In a select procedure, the
SUSPEND statement returns current values of output parameters to the calling program and continues execution. If an output parameter has not been assigned a value, its value is unpredictable, which can lead to errors. A procedure should ensure that all output parameters are assigned values before a
In both select and executable procedures,
EXIT jumps program control to the final
END statement in the procedure.
What happens when a procedure reaches the final END statement depends on the type of procedure:
- In a select procedure, the final
ENDstatement returns control to the application and sets
SQLCODEto 100, which indicates there are no more rows to retrieve.
- In an executable procedure, the final
ENDstatement returns control and values of output parameters, if any, to the calling application.
The behavior of these statements is summarized in the following table:
Jumps to final
Jumps to final
Consider the following procedure:
CREATE PROCEDURE P RETURNS (R INTEGER) AS BEGIN R = 0; WHILE (R < 5) DO BEGIN R = R + 1; SUSPEND; IF (R = 3) THEN EXIT; END END ;
If this procedure is used as a select procedure, for example:
SELECT * FROM P;
then it returns values 1, 2, and 3 to the calling application, since the
SUSPEND statement returns the current value of
R to the calling application. The procedure terminates when it encounters
If the procedure is used as an executable procedure, for example:
EXECUTE PROCEDURE P;
then it returns 1, since the
SUSPEND statement terminates the procedure and returns the current value of
R to the calling application. This is not recommended, but is included here for comparison.
When a procedure encounters an error—either a
GDSCODE error, or user-defined exception—all statements since the last
SUSPEND are undone.
Since select procedures can have multiple
SUSPENDs, possibly inside a loop statement, only the actions since the last
SUSPEND are undone. Since executable procedures should not use
SUSPEND, when an error occurs the entire executable procedure is undone (if
EXIT is used, as recommended).