2
0
During this post we will discuss about the setting of SESSION parameter through the Snowflake JavaScript procedure. As per the requirement we need to set QUERY_TAG SESSION parameter to identify the queries related to the particular module say CRM. So if we need to identify all the Queries belongs to the CRM we will set the QUERY_TAG parameter in below way:
ALTER SESSION SET QUERY_TAG = ‘” <<CRM_VERIFY_TAG>>”;
But what about, if we need to set this parameter via stored procedure, Will Developing the procedure with Owner Rights allows to successful execution of proc. Lets see.
Output:
Owner’s rights stored procedures are not permitted to change session state.
In order to make it works and set the SESSION parameter inside the Stored Procedure we need to define the procedure as CALLER.
create or replace procedure get_query_tag(BATCH_ID varchar)
returns varchar
not null
language javascript
execute as caller
as
$$
var row_count = 0;
var tag = BATCH_ID + "_VERIFY_TAG";
var sql_session = "ALTER SESSION SET QUERY_TAG = '" + tag + "'";
var sql_command = "select count(*) from customers";
var qstmt = snowflake.createStatement(
{
sqlText: sql_session
}
);
qstmt.execute();
var stmt = snowflake.createStatement(
{
sqlText: sql_command
}
);
var res = stmt.execute();
// Get back the row count. Specifically, …
// … first, get the first (and in this case only) row from the
// result set …
res.next();
// … then extract the returned value (which in this case is the
// number of rows in the table).
row_count = res.getColumnValue(1);
return row_count;
$$
;
If a caller’s rights stored procedure makes changes to the session, those changes can persist after the end of the CALL
So we can see Caller’s Rights and Owner’s Rights significance inside the Stored Procedures.
