create or replace procedure Proc_Name("Trgt_DB" VARCHAR, "Trgt_Schema" VARCHAR, "Trgt_Stage" VARCHAR, "Trgt_Timeframe" VARCHAR, "Trgt_Number_Timeframe" FLOAT)
returns string
language JavaScript
EXECUTE AS CALLER
as
$$
--select all data from table
var select_stmt = `SELECT * FROM ` + Trgt_DB + `.schema.data_table ORDER BY ID`;
var select_stmt_sql = { sqlText: select_stmt };
var select_stmt_create = snowflake.createStatement(select_stmt_sql);
var select_stmt_exec = select_stmt_create.execute();
--loop throgh all the external tables
while (select_stmt_exec.next()) {
var TGT_SCHEMA_NAME = select_stmt_exec.getColumnValue(5);
var TGT_TABLE_NAME = select_stmt_exec.getColumnValue(6);
--delete data from target tables
var remove_data_tbl = `delete from ` +TGT_SCHEMA_NAME+ `.` +TGT_TABLE_NAME+ ` where SYS_DATE <= DATEADD(` +Trgt_Timeframe+ `,-` +Trgt_Number_Timeframe+ `, current_date())`;
var remove_data_tbl_sql = {sqlText: remove_data_tbl };
var remove_data_tbl_create = snowflake.createStatement(remove_data_tbl_sql);
var remove_details_exec = remove_data_tbl_create.execute();
CALL Proc_Name('Dev', 'ACQ', 'stage', 'days', 60)
--I have a table with all of the target schemas, (column 5) and target tables (column 6) in it. I want to create a loop which iterates through each target table and deletes the data from the target table which is past a certain timeframe.
--The code I have at the moment will only delete data from the first target table in the table