While loop through each row of a table stored procedure snowflake

Viewed 298
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

0 Answers
Related