Snowflake supports JavaScript stored procedures. You may choose to start by copying and modifying a sample Snowflake stored procedure from the documentation, often this one.
As you add more SQL statements, exception handling and increase code complexity, having all code in the main JavaScript function risks becoming spaghetti code.
Fortunately, Snowflake stored procedures allow more than one function. In JavaScript, helper functions are additional functions called from a main function.
It’s easy to write a helper function. Just before the main function’s final curly bracket, add the following:
function HelperFunction(stringIn) {
//Do something here, and then return the value:
var s = stringIn;
return s;
}
You can also use the Snowflake Stored Procedure API inside helper functions. Here two helper functions using the Snowflake SP API that make your main function more readable and modular. ExecuteNonQuery executes a DML statement or SQL statement that does not return a table. ExecuteSingleValueQuery fetches the first row’s value for a specified column. You can use this to retrieve flags and settings or other values from control tables.
create or replace procedure SampleSP()
returns string
language javascript
as
$$
var s;
try{
ExecuteNonQuery("create or replace table MY_NATION_TABLE like SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;");
ExecuteNonQuery("insert into MY_NATION_TABLE select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;");
s = ExecuteSingleValueQuery("N_NAME", "select * from MY_NATION_TABLE where N_NATIONKEY = 24;");
ExecuteNonQuery("drop table MY_NATION_TABLE;");
return s;
}
catch(err){
return err;
}
// ----------------------------------------------------------------------------------
// Main function above; helper functions below
function ExecuteNonQuery(queryString) {
var out = '';
cmd1 = {sqlText: queryString};
stmt = snowflake.createStatement(cmd1);
var rs;
try{
rs = stmt.execute();
rs.next();
out = "SUCCESS: " + rs.getColumnValue(1);
}
catch(err) {
throw "ERROR: " + err.message.replace(/\n/g, " ");
}
return out;
}
function ExecuteSingleValueQuery(columnName, queryString) {
var out;
cmd1 = {sqlText: queryString};
stmt = snowflake.createStatement(cmd1);
var rs;
try{
rs = stmt.execute();
rs.next();
return rs.getColumnValue(columnName);
}
catch(err) {
if (err.message.substring(0, 18) == "ResultSet is empty"){
throw "ERROR: No rows returned in query.";
} else {
throw "ERROR: " + err.message.replace(/\n/g, " ");
}
}
return out;
}
$$;
call SampleSP();