CODEWITHSUNDEEP
stored-proceduressnowflake-cloud-data-platform
user13977932
user13977932

Reputation: 13

How to alter session parameters within a Snowflake stored procedure

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;
    }
$$
;

Upvotes: 1

Views: 3021

Answers (1)

Mike Gohl
Mike Gohl

Reputation: 737

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.

Upvotes: 2

Related Questions