Snowflake Stored Procedure- Multiple multiline SQL commands

Question

Looking to figure out best practice for a snowflake stored procedure of sequential sql commands. Currently this is the working format I have mocked up. Where it has the var cmd followed by the sqlText create statement and then the sql.execute. I know I can do a one line var cmd for sql but the readability leaves a lot to be desired. Any help/tips would be greatly appreciated.

CREATE OR REPLACE PROCEDURE example_sp()
returns string not null
language javascript
AS
$$
var cmd1 = `MERGE INTO...
            example 1st multiline SQL command`
var sql1 = snowflake.createStatement({sqlText: cmd1});
var result1 = sql.execute();

var cmd2 = `MERGE INTO...
            example 2nd multiline SQL command`
var sql2 = snowflake.createStatement({sqlText: cmd2});
var result2 = sql2.execute();

/*repeat with above template for rest of commands*/
return '1';
$$;

ferr2019 ferr2019 · Accepted Answer · 2020-12-01T17:04:33

Looks like I wasn't looking quite hard enough

var rs = snowflake.execute( { sqlText: 
 `create or replace table deleteme as
   select sysdate() as sysd;`
   } );
return 'Done.';

seems to do the trick from this question https://stackoverflow.com/a/64825664/14743506

Snowflake Stored Procedure- Multiple multiline SQL commands

1 Answers