FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

SUSPEND

Passes output to the buffer and suspends execution while waiting for caller to fetch it

Syntax
SUSPEND;

The SUSPEND statement is used in selectable stored procedures to pass the values of output parameters to a buffer and suspend execution.Execution remains suspended until the calling application fetches the contents of the buffer.Execution resumes from the statement directly after the SUSPEND statement.In practice, this is likely to be a new iteration of a looping process.

Important
Important Notes

  1. The SUSPEND statement can only occur in stored procedures or sub-procedures

  2. The presence of the SUSPEND keyword defines a stored procedure as a selectable procedure

  3. Applications using interfaces that wrap the API perform the fetches from selectable procedures transparently.

  4. If a selectable procedure is executed using EXECUTE PROCEDURE, it behaves as an executable procedure.When a SUSPEND statement is executed in such a stored procedure, it is the same as executing the EXIT statement, resulting in immediate termination of the procedure.

  5. SUSPEND“breaks” the atomicity of the block in which it is located.If an error occurs in a selectable procedure, statements executed after the final SUSPEND statement will be rolled back.Statements that executed before the final SUSPEND statement will not be rolled back unless the transaction is rolled back.

SUSPEND Examples

Using the SUSPEND statement in a selectable procedure
CREATE PROCEDURE GEN_100
  RETURNS (I INTEGER)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END
See also

[fblangref50-psql-exit]