LEAVE statement

Leaves a compound statement or loop.

Syntax

LEAVE [ statement-label ]

Remarks

The LEAVE statement is a control statement that leaves a labeled (statement-label) compound statement or a labeled loop. LEAVE can take a label that is either the beginning of a loop or a BEGIN...END block. If no statement-label is not specified, then LEAVE exits the innermost loop not the innermost BEGIN...END block. Execution resumes at the first statement after the compound statement or loop.

Using the LEAVE statement without a label is equivalent to using the Transact-SQL BREAK statement.

A compound statement that is the body of a procedure or trigger has an implicit label that is the same as the name of the procedure or trigger.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Part of optional Language Feature P002, "Computational completeness".

Example

The following fragment shows how the LEAVE statement is used without a label to leave a loop.

SET i = 1;
lbl:
LOOP
   INSERT
   INTO Counters ( number )
   VALUES ( i );
   IF i >= 10 THEN
      LEAVE;
   END IF;
   SET i = i + 1
END LOOP lbl

The following fragment uses LEAVE in a nested loop.

outer_loop:
LOOP
   SET i = 1;
   inner_loop:
   LOOP
      ...
      SET i = i + 1;
      IF i >= 10 THEN
         LEAVE outer_loop
      END IF
   END LOOP inner_loop
END LOOP outer_loop