Using SUSPEND, EXIT, and END With Procedures

From InterBase

Go Up to The Procedure Body


The 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 SUSPEND.

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 END statement returns control to the application and sets SQLCODE to 100, which indicates there are no more rows to retrieve.
  • In an executable procedure, the final END statement returns control and values of output parameters, if any, to the calling application.

The behavior of these statements is summarized in the following table:

SUSPEND, EXIT, and END

Procedure type

SUSPEND

EXIT

END

Select procedure

  • Suspends execution of procedure until next FETCH
  • Returns values

Jumps to final END

  • Returns control to application
  • Sets SQLCODE to 100

Executable ­procedure

  • Jumps to final END
  • Not recommended

Jumps to final END

  • Returns values
  • Returns control to application

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 EXIT.

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.

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.

Error behavior

When a procedure encounters an error—either a SQLCODE error, 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).

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Using_SUSPEND,_EXIT,_and_END_With_Procedures&oldid=30941"