Set session variable within a Snowflake procedure based on the procedure argument

Question

I have a view in Snowflake that uses session variables (date ranges) within it. I want to call the view from a procedure and define the session variables based on the arguments passed to the procedure. Unfortunately the below isn't working....any ideas?

CREATE PROCEDURE TEST_PROCEDURE(DATE_FROM_a DATE, DATE_TO_a DATE)
  RETURNS TABLE()
  LANGUAGE SQL
  EXECUTE AS CALLER
  AS 
  $$
  DECLARE
  SET DATE_FROM = :DATE_FROM_a;
  res resultset default (SELECT TOP 100 * FROM v_TEST);
  BEGIN
  return table(res);
  END;
  $$
  ;

Answer 1

The way it's written in your test, it's not actually executing the SET command statement. You can move the SET command statement to after the BEGIN. That will set the session variable. Note that setting session variables in stored procedures is only supported when executing with callers rights.

create or replace table v_test (s string);

CREATE or replace PROCEDURE TEST_PROCEDURE(DATE_FROM_a DATE, DATE_TO_a DATE)
  RETURNS TABLE()
  LANGUAGE SQL
  EXECUTE AS CALLER
  AS 
  $$
  DECLARE
  res resultset default (SELECT TOP 100 * FROM v_TEST);
  BEGIN
  SET DATE_FROM = :DATE_FROM_a;
  return table(res);
  END;
  $$
  ;
  
call test_procedure('2012-12-12', '2020-05-01');

select $DATE_FROM;