Time to Mobilize
Free Assessment Tool

How to use '$$' inside a Snowflake procedure

by Mauricio Rojas, on Apr 5, 2022 6:55:17 AM

This may be a small post, but hopefully, it will save you some time.

Recently, I had the problem where I had a procedure in Snowflake that was using the '$$' on a literal.

Lets say something like: INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE$$SOMEVALUE';

My stored procedure was something simple:

CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS $$
snowflake.execute(sqlText:"INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE$$SOMEVALUE'");
$$

Everything should work, right? Tragically, this was the result:

SQL compilation error: syntax error line 3 at position 71 unexpected 'SOMEVALUE'. parse error line 5 at position 3 near '<EOF>'. parse error line 5 at position 3 near '<EOF>'.

The solution was simple. Just use an escape code like '\$' for example:

CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS $$
snowflake.execute(sqlText:"INSERT INTO TABLE1(COL1) VALUES 'SOMEVALUE\$\$SOMEVALUE'");
$$

And now you won't get any errors.

Topics:Snowflake

Comments

Subscribe to Mobilize.Net Blog

More...
FREE CODE ASSESSMENT TOOL