Using Variables in Query Criteria in a Snowflake Scripting Stored Procedure

user_18227599 0
Viewed 146

In the following query I am using a stored procedure to create a table output_tbl_name from a source_tbl. The name of the output_tbl_name is timestamped with today's date.

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) as 
select * from source_tbl;'
         ,'<output_tbl_name>', :output_tbl_name);         ;    

   EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;

However, I would like to set a dynamic criteria for the data that is copied over. E.g., the source_tbl has a date column, and I want to only copy over records for dates > 6 months ago:

declare start_date date := add_months(current_date(), -6) 
create or replace output_tbl_name as select * from source_tbl where date > <start_date>

How can I incorporate this into my above query? Placing it directly into my QUERY in my BEGIN statement isn't working. Thanks for your help!

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

create a source data table:

CREATE OR REPLACE TABLE tmp_table(col1 string);

DECLARE
   creation_date   STRING := to_varchar(current_date(), 'YYYYMMDD');
   output_tbl_name STRING := concat('my_database.my_schema.', 'output_', :creation_date);
   QUERY           STRING;
   source_tbl STRING := 'TMP_TABLE';
BEGIN
  LET date_present := false;
  
  SELECT true INTO :date_present FROM INFORMATION_SCHEMA.columns WHERE table_name = :source_tbl AND column_name = 'DATE';
    
   if (date_present = true) then
       QUERY := 'create or replace table <output_tbl_name>(col1 varchar, col2 varchar) as select * from source_tbl where date > <start_date>;';
             
    ELSE 
       QUERY:= 'create or replace table <output_tbl_name>(col1 varchar, col2 varchar) as select * from source_tbl;';    
    END IF;
    
     QUERY:= REPLACE( QUERY, '<output_tbl_name>', :output_tbl_name);      
   --EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;

begin
  let count := true;
  if (count = true) then
    return 'negative value';
  elseif (count = 0) then
    return 'zero';
  else
    return 'positive value';
  end if;
end;
anonymous block
create or replace table my_database.my_schema.output_20220307(col1 varchar, col2 varchar) as select * from source_tbl;

put in a date column:

CREATE OR REPLACE TABLE tmp_table(col1 string, date timestamp);
anonymous block
create or replace table my_database.my_schema.output_20220307(col1 varchar, col2 varchar) as select * from source_tbl where date > <start_date>;
user_439920
Related