SUSPEND
Go Up to Procedures and Triggers
Suspends execution of a select procedure until the next FETCH is issued and returns values to the calling application. Available in stored procedures only.
SUSPEND;
Description: The SUSPEND statement:
- Suspends execution of a stored procedure until the application issues the next
FETCH. - Returns values of output parameters, if any.
A procedure should ensure that all output parameters are assigned values before a SUSPEND.
SUSPEND should not be used in an executable procedure. Use EXIT instead to indicate to the reader explicitly that the statement terminates the procedure.
The following table summarizes the behavior of SUSPEND, EXIT, and END.
| Procedure type | SUSPEND
|
EXIT
|
END
|
|---|---|---|---|
|
Select procedure |
|
Jumps to final |
|
|
Executable procedure |
|
Jumps to final |
|
If a SELECT procedure has executable statements following the last SUSPEND in the procedure, all of those statements are executed, even though no more rows are returned to the calling program. The procedure terminates with the final END statement, which sets SQLCODE to 100.
The SUSPEND statement also delimits atomic statement blocks in select procedures. If an error occurs in a select procedure—either a SQLCODE error, GDSCODE error, or exception—the statements executed since the last SUSPEND are undone. Statements before the last SUSPEND are never undone, unless the transaction comprising the procedure is rolled back.
Example: The following procedure illustrates the use of SUSPEND and EXIT:
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 in isql, for example,
SELECT * FROM P;
then it will return values 1, 2, and 3 to the calling application, since the SUSPEND statement returns the current value of r to the calling application until r = 3, when the procedure performs an EXIT and terminates.
If the procedure is used as an executable procedure in isql, for example,
EXECUTE PROCEDURE P;
then it will return 1, since the SUSPEND statement will terminate the procedure and return the current value of r to the calling application. Since SUSPEND should not be used in executable procedures, EXIT would be used instead, indicating that when the statement is encountered, the procedure is exited.