BEGIN … END (Snowflake Scripting)¶

BEGIN and END define a Snowflake Scripting block.

For more information on blocks, see Understanding blocks in Snowflake Scripting.

Syntax¶

BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END;

Copy

Where:

statement
A statement can be any of the following:

A single SQL statement (including CALL).

A control-flow statement (e.g. looping or branching statement).

A nested block.

exception_handler
Specifies how exceptions should be handled. Refer to Handling exceptions and EXCEPTION (Snowflake Scripting).

Usage notes¶

The keyword END must be followed immediately by a semicolon, or followed immediately by a label that is immediately followed by a semicolon.
The keyword BEGIN must not be followed immediately by a semicolon.
BEGIN and END are usually used inside another language construct, such as a looping or branching construct, or inside a stored procedure. However, this is not required. A BEGIN/END block can be the top-level construct inside an anonymous block.
Blocks can be nested.

Examples¶

This is a simple example of using BEGIN and END to group related statements. This example creates two related tables.

EXECUTE IMMEDIATE $$
BEGIN
    CREATE TABLE parent (ID INTEGER);
    CREATE TABLE child (ID INTEGER, parent_ID INTEGER);
    RETURN 'Completed';
END;
$$
;

Copy

The next example is similar; the statements are grouped into a block and are also inside a transaction within that block:

EXECUTE IMMEDIATE $$
BEGIN
    BEGIN TRANSACTION;
    TRUNCATE TABLE child;
    TRUNCATE TABLE parent;
    COMMIT;
    RETURN '';
END;
$$
;

Copy

In this example, the statements are inside a branching construct.

IF (both_rows_are_valid) THEN
    BEGIN
        BEGIN TRANSACTION;
        INSERT INTO parent ...;
        INSERT INTO child ...;
        COMMIT;
    END;
END IF;

Copy