BEGIN … END (Snowflake Scripting)¶

Syntax¶

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

Where:

statement

A statement can be any of the following:

• A single SQL statement (including CALL).

• A control-flow statement (for example, a 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;
$$
;

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;
$$
;

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;