雪花云数据平台-检索变量值并插入到另一个表中

Question

我可以从下面的语句中检索before(0(和after count(4(的值，但当我从下面的代码中使用这些变量(load_cnt_before、load_cnt_after(并引用它们以将值插入到表中时，它表示找不到变量(请参阅下面的错误(。如何使用这些值将它们插入表中。

错误：存储过程中的执行错误REC_COUNT_CHECK:SQL编译错误：错误行1位置114无效标识符'LOAD_CNT_BEFORE'在Statement.execute，行25位置90

代码：

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();

Answer 1

JS变量应该传递到SQL查询中。该机制称为绑定变量

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();