Understanding Blocks in Snowflake Scripting

In Snowflake Scripting, you write procedural code within a Snowflake Scripting block. This topic explains how to write procedural code in a block.

Understanding the Structure of a Block

A block has the following basic structure:

DECLARE
  ... (variable declarations, cursor declarations, etc.) ...
BEGIN
  ... (Snowflake Scripting and SQL statements) ...
EXCEPTION
  ... (statements for handling exceptions) ...
END;
Copy

A block consists of sections (some of them optional) that are delimited by keywords. Each section serves a different purpose:

  • DECLARE: If you need to use any variables, cursors, etc. in the block, you can either declare these in the DECLARE section of the block or in the BEGIN ... END section of the block.

    You can declare:

    This section of the block is optional.

  • BEGIN … END: Write SQL statements and Snowflake Scripting constructs in the section of the block between BEGIN and END.

  • EXCEPTION: If you need to add exception handling code, add this to the EXCEPTION section of the block.

    This section of the block is optional.

A simple block just requires the keywords BEGIN and END. For example:

BEGIN
    CREATE TABLE employee (id INTEGER, ...);
    CREATE TABLE dependents (id INTEGER, ...);
END;
Copy

Important

The keyword BEGIN that starts a block is different from the keyword BEGIN that starts a transaction. To minimize confusion, Snowflake strongly recommends starting transactions with BEGIN TRANSACTION (or the older form BEGIN WORK), rather than merely BEGIN.

Any database objects that you create in a block (e.g. the tables in the example above) can be used outside of the block.

If the code uses variables, you can declare those variables in the block. One way to do this is in the DECLARE section of the block. For example:

DECLARE
    radius_of_circle FLOAT;
    area_of_circle FLOAT;
BEGIN
    radius_of_circle := 3;
    area_of_circle := pi() * radius_of_circle * radius_of_circle;
    RETURN area_of_circle;
END;
Copy

This example declares a variable, uses the variable, and returns the value of the variable. (For details on how values are returned from a block, see Returning a Value.)

Note that these variables cannot be used outside of the block. See Understanding the Scope of Declarations.

You can also declare a variable in the BEGIN ... END section of the block by using LET. For details, see Declaring a Variable.

Using a Block in a Stored Procedure

You can use a block in the definition of a stored procedure. The following is an example that you can run in Snowsight to create a stored procedure containing a Snowflake Scripting block:

CREATE OR REPLACE PROCEDURE area()
  RETURNS FLOAT
  LANGUAGE SQL
  AS
    DECLARE
      radius FLOAT;
      area_of_circle FLOAT;
    BEGIN
      radius := 3;
      area_of_circle := PI() * radius * radius;
      RETURN area_of_circle;
    END;
Copy

Note that if you are using SnowSQL or the Classic Console, you must use single quotes or double dollar signs to delimit the block, as explained in Using Snowflake Scripting in SnowSQL and the Classic Console:

CREATE OR REPLACE PROCEDURE area()
RETURNS FLOAT
LANGUAGE SQL
AS
$$
DECLARE
    radius FLOAT;
    area_of_circle FLOAT;
BEGIN
    radius := 3;
    area_of_circle := PI() * radius * radius;
    RETURN area_of_circle;
END;
$$
;
Copy

Using an Anonymous Block

If you don’t want to store the block in a stored procedure in the database, you can define and use an anonymous block. An anonymous block is a block that is not part of a stored procedure. You define the block as a separate, standalone SQL statement.

The BEGIN statement that defines the block also executes the block. (You don’t run a separate CALL command to execute the block.)

The following is an example of an anonymous block that you can run in Snowsight:

DECLARE
  radius_of_circle FLOAT;
  area_of_circle FLOAT;
BEGIN
  radius_of_circle := 3;
  area_of_circle := PI() * radius_of_circle * radius_of_circle;
  RETURN area_of_circle;
END;
Copy

The example produces the following output:

+-----------------+
| anonymous block |
|-----------------|
|    28.274333882 |
+-----------------+
Copy

Note that if you are using SnowSQL or the Classic Console, you must specify the block as a string literal (delimited by single quotes or double dollar signs), and you must pass the block to the EXECUTE IMMEDIATE command, as explained in Using Snowflake Scripting in SnowSQL and the Classic Console:

EXECUTE IMMEDIATE $$
DECLARE
    radius_of_circle FLOAT;
    area_of_circle FLOAT;
BEGIN
    radius_of_circle := 3;
    area_of_circle := PI() * radius_of_circle * radius_of_circle;
    RETURN area_of_circle;
END;
$$
;
+-----------------+
| anonymous block |
|-----------------|
|    28.274333882 |
+-----------------+
Copy

Note that the column header in the output is “anonymous block”; if the code had been executed in a stored procedure, the column header would have been the name of the stored procedure.

The following example defines an anonymous block that creates two tables that are related. In this example, the block of procedural code does not need to use variables, so the DECLARE section of the block is omitted.

BEGIN
  CREATE TABLE parent (ID INTEGER);
  CREATE TABLE child (ID INTEGER, parent_ID INTEGER);
  RETURN 'Completed';
END;
Copy
+-----------------+
| anonymous block |
|-----------------|
| Completed       |
+-----------------+
Copy

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
BEGIN
    CREATE TABLE parent (ID INTEGER);
    CREATE TABLE child (ID INTEGER, parent_ID INTEGER);
    RETURN 'Completed';
END;
$$
;
+-----------------+
| anonymous block |
|-----------------|
| Completed       |
+-----------------+
Copy