Using Snowflake Scripting in SnowSQL and the Classic Console

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

Note

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

Introduction

Currently, SnowSQL and the Classic Console do not correctly parse Snowflake Scripting blocks. 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, you must use delimiters around the start and end of a Snowflake Scripting block if you are using SnowSQL or the Classic Console.

The following sections explain how to do this:

Using String Constant Delimiters Around a Block in a Stored Procedure

If you are creating a stored procedure in SnowSQL or the Classic Console, 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
Language: English