CODEWITHSUNDEEP
proceduresnowflake-cloud-data-platform
RLT
RLT

Reputation: 191

Snowflake - First Proc - What is wrong with Syntax

CREATE OR REPLACE PROCEDURE "PUBLIC".PHONE_DATA_ROLLUP()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
    var rs = snowflake.execute( { sqlText: 
      'INSERT INTO PROC_LOG ("PROC_NAME","EXEC_TIME") SELECT ''value 1'' AS "PROC_NAME", CURRENT_TIMESTAMP as "EXEC_TIME";'
       });
   return 'Done.';
  $$;


CALL PHONE_DATA_ROLLUP();

Error:

SQL Error [100131] [P0000]: JavaScript compilation error: Uncaught SyntaxError: Unexpected string in PHONE_DATA_ROLLUP at ' 'INSERT INTO PROC_LOG ("PROC_NAME","EXEC_TIME") SELECT ''VALUE 1'' AS "PROC_NAME", CURRENT_DATE as "EXEC_TIME";'' position 62

Upvotes: 1

Views: 986

Answers (2)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2850

I would highly recommend using backquotes for SQL strings in a JavaScript procedure, as well as variable binding, eg

var rs = snowflake.execute( {
  sqlText:  `INSERT INTO PROC_LOG ("PROC_NAME","EXEC_TIME")
             SELECT :1 AS "PROC_NAME", CURRENT_TIMESTAMP as "EXEC_TIME"`,
  binds:    ["value 1"]
});

The main advantages are:

  1. You can use both 'single' and "double" quotes without any protection contamination
  2. Strings can be multiline

Both features are highly useful with SQL code.

Upvotes: 3

David Garrison
David Garrison

Reputation: 2880

in Javascript like this you can escape single quotes with a backslash (\') instead of double single quotes ('')

...
'INSERT INTO PROC_LOG ("PROC_NAME","EXEC_TIME") SELECT \'value 1\' AS "PROC_NAME", CURRENT_TIMESTAMP as "EXEC_TIME";'
...

Upvotes: 2

Related Questions