BREAK

BREAK (or EXIT) terminates a loop.

For more information on terminating loops, see Terminating a Loop.

See also

CONTINUE

Syntax

{ BREAK | EXIT } [ <label> ] ;

Where:

label

An optional label. If the label is specified, the BREAK will jump to the statement immediately after the label.

You can use this to break out of more than one level of a nested loop or a nested branch.

Usage Notes

  • BREAK and EXIT are synonymous.

  • If the loop is embedded in another loop(s), you can exit out of not only the current loop, but also an enclosing loop, by including the enclosing loop’s label as part of the BREAK. For an example, see the examples section below.

Examples

Here is an example of using BREAK to exit not only the current loop, but also an enclosing loop:

DECLARE
  i INTEGER;
  j INTEGER;
BEGIN
  i := 1;
  j := 1;
  WHILE (i <= 4) DO
    WHILE (j <= 4) DO
      -- Exit when j is 3, even if i is still 1.
      IF (j = 3) THEN
        BREAK outer_loop;
      END IF;
      j := j + 1;
    END WHILE inner_loop;
    i := i + 1;
  END WHILE outer_loop;
  -- Execution resumes here after the BREAK executes.
  RETURN i;
END;

Note: If you are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

EXECUTE IMMEDIATE $$
    DECLARE
        i INTEGER;
        j INTEGER;
    BEGIN
        i := 1;
        j := 1;
        WHILE (i <= 4) DO
            WHILE (j <= 4) DO
                -- Exit when j is 3, even if i is still 1.
                IF (j = 3) THEN
                     BREAK outer_loop;
                END IF;
                j := j + 1;
            END WHILE inner_loop;
            i := i + 1;
        END WHILE outer_loop;
        -- Execution resumes here after the BREAK executes.
        RETURN i;
    END;
$$;

Here is the output:

+-----------------+
| anonymous block |
|-----------------|
|               1 |
+-----------------+
Back to top