1

I can't get a multiline SQL statement to work within a procedure/javascript without putting \ after each line.

This works:

CREATE or replace PROCEDURE PR_DELETEME()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: 
  'create or replace table deleteme as select sysdate() as my_date;'
   } );
return 'Done.';
$$;

This fails:

CREATE or replace PROCEDURE PR_DELETEME()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: 
  'create or replace table deleteme as
   select sysdate() as my_date;'
   } );
return 'Done.';
$$;

call PR_DELETEME(); gives...

 JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in PR_DELETEME at ' 'create or replace table deleteme as' position 6

I really don't want to have to put \ at the end of each line.

Jason Scarlett
  • 21
  • 4
  • Does this answer your question? [Creating multiline strings in JavaScript](https://stackoverflow.com/questions/805107/creating-multiline-strings-in-javascript) – computercarguy Nov 13 '20 at 16:43
  • Or this one: https://stackoverflow.com/questions/33647043/convenient-way-to-wrap-long-sql-statements-in-javascript – computercarguy Nov 13 '20 at 16:43
  • It does, but adding either a \ at the end of every line or wrapping each line in "one line of code"+ is tedious and in my opinion messy. I see examples elsewhere where they did not do this, not sure how they got away with it. – Jason Scarlett Nov 13 '20 at 17:41
  • For example, Snowflkake's own example doesn't do this: https://docs.snowflake.com/en/sql-reference/sql/create-procedure.html – Jason Scarlett Nov 13 '20 at 17:44
  • 1
    Even though there's some contention over what the max length of a line of code should be, but spacing long lines of code over multiple lines is considered better practice than making really long lines that never seem to end. I've dealt with lines that were upwards of 1000 characters, and they were atrocious to read as well as debug. https://stackoverflow.com/questions/110928/is-there-a-valid-reason-for-enforcing-a-maximum-width-of-80-characters-in-a-code ad https://softwareengineering.stackexchange.com/questions/604/is-the-80-character-limit-still-relevant-in-times-of-widescreen-monitors – computercarguy Nov 13 '20 at 17:47
  • GOT IT! .... you have to use "backticks" ... ` (<< the key with the tilda) not single quotes like this>> ' – Jason Scarlett Nov 13 '20 at 17:48
  • Also, those examples were likely done within a SQL editor, not JavaScript. They don't even include the JS, so you aren't seeing how they really format the code. Not to mention that most other SQL tutorials use the same format when showing it's use cases. – computercarguy Nov 13 '20 at 17:49

1 Answers1

1

Use "backticks" not single quotes or double quotes. Below now works:

CREATE or replace PROCEDURE PR_DELETEME()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: 
  `create or replace table deleteme as
   select sysdate() as sysd;`
   } );
return 'Done.';
$$;
Jason Scarlett
  • 21
  • 4
  • 1
    The use of these backticks are called "string literals" or "template literals". Many programming languages have sometime similar. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals – computercarguy Nov 13 '20 at 17:51
  • I obviously can't read and didn't know what they were saying when they said ... "The example below uses $$ and backquotes because the body of the stored procedure contains single quotes and double quotes" ... my bad. – Jason Scarlett Nov 13 '20 at 17:54