BREAK (Snowflake Scripting) | Snowflake Documentation (original) (raw)

ReferenceScripting referenceBREAK

BREAK (or EXIT) terminates a loop.

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

See also:

CONTINUE

Syntax¶

{ BREAK | EXIT } [ ] ;

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 use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE >DECLARE>iINTEGER;>jINTEGER;>BEGIN>i:=1;>j:=1;>WHILE(i<=4)DO>WHILE(j<=4)DO>−−Exitwhenjis3,evenifiisstill1.>IF(j=3)THEN>BREAKouterloop;>ENDIF;>j:=j+1;>ENDWHILEinnerloop;>i:=i+1;>ENDWHILEouterloop;>−−ExecutionresumeshereaftertheBREAKexecutes.>RETURNi;>END;>> 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; >>DECLARE>iINTEGER;>jINTEGER;>BEGIN>i:=1;>j:=1;>WHILE(i<=4)DO>WHILE(j<=4)DO>−−Exitwhenjis3,evenifiisstill1.>IF(j=3)THEN>BREAKouterl​oop;>ENDIF;>j:=j+1;>ENDWHILEinnerl​oop;>i:=i+1;>ENDWHILEouterl​oop;>−−ExecutionresumeshereaftertheBREAKexecutes.>RETURNi;>END;>;

Here is the output:

+-----------------+

anonymous block
1
+-----------------+