Retrieve the Variable value and insert into another table

Question

I can retrieve the values of before(0) and after counts(4) from the below statements, but when I make use of those variables (load_cnt_before, load_cnt_after) from the code below and refer them to have the values inserted into a table it says it cant find the variables(refer to error below). How can I use those values to INSERT them into table.

Error: Execution error in stored procedure REC_COUNT_CHECK: SQL compilation error: error line 1 at position 114 invalid identifier 'LOAD_CNT_BEFORE' At Statement.execute, line 25 position 90

Code:

CREATE OR REPLACE PROCEDURE REC_COUNT_CHECK()       
RETURNS VARCHAR LANGUAGE JAVASCRIPT   
AS $$ 
 
    
    /***** Get the Record Count before Refresh ****/  
        var load_cnt=`SELECT Count(*) as record_cnt from "PLNG_ANALYSIS"."LOADDATA"."LOAD_VERIFICATION" WHERE EXTRACTDATE=Current_date()-1 ;`
        var load_cnt_check = snowflake.createStatement({sqlText: load_cnt}).execute();
        load_cnt_check.next();
        load_cnt_before = load_cnt_check.getColumnValue(1);
        

/***** Execute the SP ****/
        var sp_call = "CALL LOAD_VERIFICATION()";    /***Refreshes data in table LOAD_VERIFICATION***/
        var result = snowflake.execute({sqlText: sp_call});  
        result.next();
        var return_msg2 = result.getColumnValue(1);
        
        /***** Check the After Refresh Count ****/
        var load_cnt_after=`SELECT Count(*) as record_cnt from "PLNG_ANALYSIS"."HFM"."LOAD_VERIFICATION" WHERE EXTRACTDATE=Current_date() ;`
        var load_cnt_check_after = snowflake.createStatement({sqlText: load_cnt_after}).execute();
        load_cnt_check_after.next();
        load_cnt_after= load_cnt_check_after.getColumnValue(1);
        
  /***** INSERT BEFORE AND AFTER COUNTS INTO LOG TABLE ****/     
         var insert_status_sp1=`INSERT INTO LOAD_STATUS_LOG_KK values (Current_TIMESTAMP(),1,'LOAD_VERIFICATION','Success','',**load_cnt_before,load_cnt_after**,1);`
            var exec_sp1_status = snowflake.createStatement({sqlText: insert_status_sp1}).execute();
            exec_sp1_status.next();
         
  
  return 'Success'
            
  $$;
  CALL REC_COUNT_CHECK();

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-06-24T06:15:45

JS variables should be passed into SQL query. The mechanism is called Binding Variables

var insert_status_sp1=`INSERT INTO LOAD_STATUS_LOG_KK values (Current_TIMESTAMP(),1,'LOAD_VERIFICATION','Success','',:1,:2,1);`
        
var exec_sp1_status = snowflake.createStatement(
         {sqlText: insert_status_sp1,binds:[load_cnt_before,load_cnt_after]}
         ).execute();

Retrieve the Variable value and insert into another table

1 Answers