title: Snowflake Stored Procedure For Loop
body: <p>i'm working with Snowflake,
  i created this Stored Procedure, it's purpose is to do the following steps:</p>
  <ol>
  <li>extract the relevant 'application_id' values from the SQL query</li>
  <li>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.</li>
  </ol>
  <p>when i call the Stored Procedure, it runs without errors, but it doesn't do anything ( the relevant records are not been deleted).</p>
  <p>i added my SP code,
  please let me know if you see any syntax / logic errors,
  thanks</p>
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>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 &lt;= 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();</code></pre>
  </div>
  </div>
  </p>