CODEWITHSUNDEEP
stored-proceduressnowflake-cloud-data-platformglobal-variablesglobal-scopeglobal-functions
Sam
Sam

Reputation: 11

Snowflake Global function and variable

I have a many (snowflake) javascript stored procedures per data pipeline , in all the procedures I have this function defined.

create or replace procedure procedure1()
returns strint
language javascript
as
'
function returnResult(sql_text){
rs = snowflake.execute({sqlText:sql_text});
rs.next();
return rs.getColumnValue(1);
}

--declare constants 
--declare variables
--perform some checks

return returnResult(`call procedure2('param1', 'param2')`);
'
;

I use this to execute sql's multiple times inside the same procedure .like

return returnResult(`call procedure2('param1', 'param2')`);

Note: I don't want to use the function call in 'select statement'

Is there a way I store this function 'returnResult' globally once and use in all the stored procedures ?

Along with this, Can I define any variable globally and use in multiple stored procedures (pipelines) ?

I tried creating UDF's but it does not look to be the right approach.

Upvotes: 0

Views: 854

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

There is currently no way to define helper functions globally for Snowflake JavaScript stored procedures or UDFs. You need to include the helper functions in every stored procedure or UDF that references them.

Since Snowflake Java stored procedures and UDFs can reference compiled JAR files, you can implement a single helper function (or in fact an entire class) and reuse that code across a number of different stored procedures. Python stored procedures and UDFs can also reference compiled pickle files, so they can reference shared functions and libraries too.

It's also possible to have Snowflake compile Java or Python stored procedures and UDFs if you do not want to deal with external IDEs or a framework like Snowpark. That way a main SP or UDF can include a number of functions that other SPs or UDFs reference.

Upvotes: 0

Related Questions