FirebirdSQL logo

LEAVE

Exits a loop

Syntax
[label:]
<loop_stmt>
BEGIN
  ...
  LEAVE [label];
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list> DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>)} DO
Table 1. LEAVE Statement Parameters
Argument Description

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement.Using the optional label parameter, LEAVE can also exit an outer loop, that is, the loop labelled with label.Code continues to be executed from the first statement after the terminated loop block.

LEAVE Examples

  1. Leaving a loop if an error occurs on an insert into the NUMBERS table.The code continues to be executed from the line C = 0.

    ...
    WHILE (B < 10) DO
    BEGIN
      INSERT INTO NUMBERS(B)
      VALUES (:B);
      B = B + 1;
      WHEN ANY DO
      BEGIN
        EXECUTE PROCEDURE LOG_ERROR (
          CURRENT_TIMESTAMP,
          'ERROR IN B LOOP');
        LEAVE;
      END
    END
    C = 0;
    ...
  2. An example using labels in the LEAVE statement.LEAVE LOOPA terminates the outer loop and LEAVE LOOPB terminates the inner loop.Note that the plain LEAVE statement would be enough to terminate the inner loop.

    ...
    STMT1 = 'SELECT NAME FROM FARMS';
    LOOPA:
    FOR EXECUTE STATEMENT :STMT1
    INTO :FARM DO
    BEGIN
      STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
      LOOPB:
      FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
      INTO :ANIMAL DO
      BEGIN
        IF (ANIMAL = 'FLUFFY') THEN
          LEAVE LOOPB;
        ELSE IF (ANIMAL = FARM) THEN
          LEAVE LOOPA;
        SUSPEND;
      END
    END
    ...