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>'
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;
$$
;
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;
$$
;
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;