Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector¶

This topic explains how to run the Snowflake Scripting examples in SnowSQL, the Classic Console, and the Python Connector.

Note

If you are using other clients and interfaces (e.g. Snowsight or the JDBC driver), you can skip this topic and refer to Snowflake Scripting blocks.

Introduction¶

Currently, the following interfaces do not correctly parse Snowflake Scripting blocks:

SnowSQL
Classic Console
The execute_stream() and execute_string() methods in Python Connector code

Note

The other Python Connector methods parse Snowflake Scripting blocks correctly.

Entering and running a Snowflake Scripting block can result in the following error:

SQL compilation error: syntax error line 2 at position 25 unexpected '<EOF>'

Copy

To work around this, use delimiters around the start and end of a Snowflake Scripting block if you are using these interfaces.

The following sections explain how to do this:

Using string constant delimiters around a block in a stored procedure
Passing a block as a string literal to EXECUTE IMMEDIATE

Using string constant delimiters around a block in a stored procedure¶

If you are creating a stored procedure, enclose the Snowflake Scripting block in single quotes or double dollar signs. For example:

CREATE OR REPLACE PROCEDURE myprocedure()
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
    -- Snowflake Scripting code
    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

Passing a block as a string literal to EXECUTE IMMEDIATE¶

If you are writing an anonymous block, pass the block as a string literal to the EXECUTE IMMEDIATE command. To delimit the string literal, use single quotes or double dollar signs.

For example:

EXECUTE IMMEDIATE $$
-- Snowflake Scripting code
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

As an alternative, you can define a session variable that is a string literal containing the block, and you can pass that session variable to the EXECUTE IMMEDIATE command. For example:

SET stmt =
$$
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;
$$
;

EXECUTE IMMEDIATE $stmt;

Copy