SUSPEND

From InterBase
Jump to: navigation, search

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.

SUSPEND, EXIT, and END
Procedure type SUSPEND EXIT END

Select procedure

  • Suspends execution of procedure until next FETCH is issued
  • Returns output values

Jumps to final END

  • Returns control to application
  • Sets SQLCODE to 100 (end of record stream)

Executable procedure

  • Jumps to final END
  • Not recommended

Jumps to final END

  • Returns values
  • Returns control to application
Note: 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.

See Also