LEAVE statement

The LEAVE statement transfers program control out of a loop or a compound statement.

Invocation

This statement can be embedded in an:
  • SQL procedure definition
  • Compound SQL (compiled) statement
  • Compound SQL (inlined) statement
The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramLEAVElabel

Description

label
Specifies the label of the compound, FOR, LOOP, REPEAT, or WHILE statement to exit.

Notes

  • When a LEAVE statement transfers control out of a compound statement, all open cursors in the compound statement, except cursors that are used to return result sets, are closed.

Example

This example contains a loop that fetches data for cursor c1. If the value of SQL variable at_end is not zero, the LEAVE statement transfers control out of the loop.

   CREATE PROCEDURE LEAVE_LOOP(OUT counter INTEGER)
     LANGUAGE SQL
     BEGIN
       DECLARE v_counter INTEGER;
       DECLARE v_firstnme VARCHAR(12);
       DECLARE v_midinit CHAR(1);
       DECLARE v_lastname VARCHAR(15);
       DECLARE at_end SMALLINT DEFAULT 0;
       DECLARE not_found CONDITION FOR SQLSTATE '02000';
       DECLARE c1 CURSOR FOR
         SELECT firstnme, midinit, lastname
           FROM employee;
       DECLARE CONTINUE HANDLER for not_found
         SET at_end = 1;
       SET v_counter = 0;
       OPEN c1;
       fetch_loop:
       LOOP
         FETCH c1 INTO v_firstnme, v_midinit, v_lastname;
         IF at_end <> 0 THEN LEAVE fetch_loop;
         END IF;
         SET v_counter = v_counter + 1;
       END LOOP fetch_loop;
       SET counter = v_counter;
       CLOSE c1;
     END