Use a variable for table/identifier name in stored procedure with Snowflake Scripting

user_18227599 0
Viewed 744

I am creating a stored procedure using Snowflake Scripting and I would like the output to be a table created with a date stamp. In SQL I would write this as

set creation_date = to_varchar(current_date(), 'YYYYMMDD');

set output_tbl_name = concat('my_database.my_schema.', 'output_', $creation_date);

create or replace table identifier($output_tbl_name) (col1 varchar, col2 varchar);

This would then create a table called output_20220216.

However if I use Snowflake Scripting in my stored procedure, the set function is not recognized. I tried using

let creation_date varchar := to_varchar(current_date(), 'YYYYMMDD');

let output_tbl_name varchar := concat('my_database.my_schema.', 'output_', $creation_date);

create or replace table identifier($output_tbl_name) (col1 varchar, col2 varchar);

But I get an error due to the $ symbol. If I use create or replace table output_tbl_name, it will just create a table called output_tbl_name.

How can I use identifiers in Snowflake Scripting?

snowflake-cloud-data-platform stored-procedures
1 Answers

Using Snowflake Scripting and EXECUTE IMMEDIATE with concatenated query:

DECLARE
   creation_date   STRING := to_varchar(current_date(), 'YYYYMMDD');
   output_tbl_name STRING := concat('my_database.my_schema.', 'output_', :creation_date);
   QUERY           STRING;
BEGIN

   QUERY:= REPLACE(          
         'create or replace table <output_tbl_name>(col1 varchar, col2 varchar);'
         ,'<output_tbl_name>', :output_tbl_name);         ;    

   EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;

Output(Classic UI):

enter image description here

SELECT * FROM my_database.my_schema.output_20220217;
-- COL1 COL2
user_50708790
Related