7.6.9. SUSPEND

Used for

Passing output to the buffer and suspending execution while waiting for caller to fetch it

Available in

PSQL

Syntax

  1. SUSPEND

The SUSPEND statement is used in a selectable stored procedure 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 Notes

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

  2. When a SUSPEND statement is executed in an executable stored procedure, it is the same as executing the EXIT statement, resulting in immediate termination of the procedure.

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

Example

Using the SUSPEND statement in a selectable procedure:

  1. CREATE PROCEDURE GEN_100
  2. RETURNS (
  3.   I INTEGER
  4. )
  5. AS
  6. BEGIN
  7.   I = 1;
  8.   WHILE (1=1) DO
  9.   BEGIN
  10.     SUSPEND;
  11.     IF (I=100) THEN
  12.       EXIT;
  13.     I = I + 1;
  14.   END
  15. END

See also

EXIT