i'm working with Snowflake, i created this Stored Procedure, it's purpose is to do the following steps:

1. extract the relevant 'application_id' values from the SQL query
2. use a FOR loop over a SQL query, with the 'application_id' values that i extracted in step 1, and delete the relevant rows from the target table.

when i call the Stored Procedure, it runs without errors, but it doesn't do anything ( the relevant records are not been deleted).

i added my SP code, please let me know if you see any syntax / logic errors, thanks

CREATE OR REPLACE PROCEDURE DWH.sp_testing()
RETURNS string
LANGUAGE javascript
strict
EXECUTE AS owner
AS
$$

try 
{
    var application_list = ` SELECT  application_id
                             FROM PUBLIC.my_source_table_name
                             WHERE my_flag = 1
                           `

    var query_statement = snowflake.createStatement({sqlText: application_list});
    var application_list_result = query_statement.execute();

    for (i = 1; i <= query_statement.getRowCount(); i  ) 
    {
        application_list_result.next()
        application_id = application_list_result.getColumnValue(1)   
         

        var delete_daily_records = `
                                DELETE FROM PUBLIC.my_target_table_name AS target
                                WHERE target.application_id = ${application_id}
                            `

        snowflake.execute({sqlText: delete_daily_records});
    }

}

catch (err) 
{
    throw "Failed: "   err; 
}

$$
;

CALL DWH.sp_testing();

CodePudding user response：

Are you sure your query is returning data? Also are you sure the target table has data matching your DELETE statement?

The following test works for me using your stored procedure:

select count(*) from citibike_trips where end_station_id=6215;

returns: 14565

Now, I created your stored proc:

CREATE OR REPLACE PROCEDURE sp_testing()
RETURNS string
LANGUAGE javascript
strict
EXECUTE AS owner
AS
$$

try 
{
    var application_list = `SELECT  end_station_id
                            FROM citibike_trips
                            WHERE end_station_id=6215 limit 10
                           `

    var query_statement = snowflake.createStatement({sqlText: application_list});
    var application_list_result = query_statement.execute();

    for (i = 1; i <= query_statement.getRowCount(); i  ) 
    {
        application_list_result.next()
        end_station_id = application_list_result.getColumnValue(1)
        
         

        var delete_daily_records = `
                                DELETE FROM citibike_trips AS target
                                WHERE target.end_station_id = ${end_station_id}
                            `

        snowflake.execute({sqlText: delete_daily_records});
    }
}
catch (err) 
{
    throw "Failed: "   err; 
}

$$
;

Run it:

CALL SP_TESTING();

Shows NULL as result (expected since it is not returning anything). But then when I check the table again:

select count(*) from citibike_trips where end_station_id=6215;

returns: 0