How to alter session parameters within a Snowflake stored procedure

Question

I'm building a date dimension with a stored procedure and need to alter session parameter WEEK_START to ensure correct calculation of certain values. On this topic, Snowflake documentation is mostly focused on distinguishing Caller vs Owner rights, but implies this should be possible. However, the result I get when calling the procedure defined below is: "Failed: Stored procedure execution error: Unsupported statement type 'ALTER_SESSION'."

CREATE OR REPLACE PROCEDURE PUBLIC.USP_ALTER_SESSION_TEST(BATCH_ID float)
returns string
language javascript
as
$$
    var sql_session,
        sql_test,
        stmt_session,
        stmt_test;
  
    sql_session = "ALTER SESSION SET WEEK_START = 1;";
    sql_test = "SELECT DAYOFWEEK(CURRENT_DATE());";
      
    try 
    {
        stmt_session = snowflake.createStatement( {sqlText: sql_session} );
        stmt_session.execute ();
        
        stmt_test = snowflake.createStatement( {sqlText: sql_test} );
        stmt_test.execute ();
        
        return "Succeeded.";
    }
    catch (err)  
    {
        return "Failed: " + err;
    }
$$
;

Mike Gohl Mike Gohl · Accepted Answer · 2020-07-22T18:47:23

There are somethings that are not when executing as owner. Week start is allowed with caller execution. Add EXECUTE AS CALLER to the definition and it should work.

How to alter session parameters within a Snowflake stored procedure

1 Answers