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