Understanding blocks in Snowflake Scripting¶
In Snowflake Scripting, you write procedural code in 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;
A block consists of required and optional sections that are delimited by keywords. Each section serves a different purpose:
DECLARE: If you need to use any variables, cursors, RESULTSETs, or exceptions 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 only requires the keywords BEGIN and END. For example:
BEGIN
CREATE TABLE employee (id INTEGER, ...);
CREATE TABLE dependents (id INTEGER, ...);
END;
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 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;
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.
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;
Note: If you are using SnowSQL, the Classic Console, or the
execute_stream
or execute_string
method in Python Connector
code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
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;
$$
;
You can call a stored procedure using the CALL command. The following example calls
the stored procedure area
in the previous example:
CALL area();
The stored procedure returns the following output:
+--------------+
| AREA |
|--------------|
| 28.274333882 |
+--------------+
Using an anonymous block¶
If you do not 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;
Note: If you are using SnowSQL, the Classic Console, or the
execute_stream
or execute_string
method in Python Connector
code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
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;
$$
;
The example produces the following output:
+-----------------+
| anonymous block |
|-----------------|
| 28.274333882 |
+-----------------+
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;
Note: If you are using SnowSQL, the Classic Console, or the
execute_stream
or execute_string
method in Python Connector
code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE $$
BEGIN
CREATE TABLE parent (ID INTEGER);
CREATE TABLE child (ID INTEGER, parent_ID INTEGER);
RETURN 'Completed';
END;
$$
;
+-----------------+
| anonymous block |
|-----------------|
| Completed |
+-----------------+