Snowflake JavaScript存储过程INSERT无执行无报错问题排查

问题背景 #

我正在开发一个Snowflake JavaScript存储过程，该过程从多张表中查询数据并插入到名为forecast的目标表，代码已添加注释便于理解：

CREATE OR REPLACE PROCEDURE forecast(SITE_ID VARCHAR, PCT FLOAT, PRODUCT VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE JAVASCRIPT
COMMENT = 'abc'
AS
$$
var sql = `WITH FINAL AS (
...
WHERE (site_id = :1)
),
future as (
select 
...
union all
select 
...
) 
SELECT
:2,
sce.site_id,
sce.subscription_id,
sce.receiver_contact,
sce.subscription_status,
sce.change_mrr_event_type,
sce.modification_time,
sce.churn_type,
sce.mrr,
sce.product_name,
sce.country_name,
sce.segment,
sce.source_name,
sce.mrr_change
from future as sce
...`;

try {
    //main vars for input parameters
    var single_quote="'";
    var Product_In = PRODUCT;
    var MRR_In = PCT;
    // we capture the most recent value of the model sequence number in the target table
    var sql_command = "select ifnull(max(model_seq),0) as model_seq from forecast";
    var stmt1 = snowflake.createStatement({ sqlText: sql_command });
    var resultSet1 = stmt1.execute();
    resultSet1.next();

    // we increment above value by 1 before going to work
    var MODEL_SEQ = resultSet1.getColumnValue(1);
    MODEL_SEQ++;

    // we execute the copy 
    var stmt2 = snowflake.createStatement({ sqlText: sql, binds: [SITE_ID, MODEL_SEQ, PCT, PRODUCT] });
    var resultSet2 = stmt2.execute();
    resultSet2.next();
    // and scroll the cursor/resultset
    // insert into the target table when true
    for (i = 1; i <= resultSet2.getRowCount(); i++) {
        SITE_ID = resultSet2.getColumnValue(2)
        SUBSCRIPTION_ID = resultSet2.getColumnValue(3)
        RECEIVER_CONTACT = resultSet2.getColumnValue(4)
        SUBSCRIPTION_STATUS = resultSet2.getColumnValue(5)
        CHANGE_MRR_EVENT_TYPE = resultSet2.getColumnValue(6)
        MODIFICATION_TIME = resultSet2.getColumnValue(7)
        CHURN_TYPE = resultSet2.getColumnValue(8)
        MRR = resultSet2.getColumnValue(9)
        PRODUCT_NAME = resultSet2.getColumnValue(10)
        COUNTRY_NAME = resultSet2.getColumnValue(11)
        SEGMENT = resultSet2.getColumnValue(12)
        SOURCE_NAME = resultSet2.getColumnValue(13)
        MRR_CHANGE = resultSet2.getColumnValue(14)

        //We check for a specific condition - if true, we process MRR
        if (CHANGE_MRR_EVENT_TYPE === 'Activation' && PRODUCT_NAME === single_quote+Product_In+single_quote)
        {
            //if true, we process MRR
            MRR_Out = (MRR + (MRR * MRR_In / 100))
            //Position based binding
            var sql_insert = "INSERT INTO FORECAST VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)";
            var stmt3 = snowflake.createStatement(
                {
                    sqlText: sql_insert,
                    binds: [MODEL_SEQ, SITE_ID, SUBSCRIPTION_ID, RECEIVER_CONTACT, SUBSCRIPTION_STATUS, CHANGE_MRR_EVENT_TYPE, MODIFICATION_TIME, CHURN_TYPE, MRR_Out, PRODUCT_NAME, COUNTRY_NAME, SEGMENT, SOURCE_NAME, MRR_CHANGE]
                }   
            );
            //insert this row
            var resultSet3 = stmt3.execute();
            resultSet3.next();  
            //next row in source data cursor
            resultSet2.next();

        }
    }

    result = "Succeeded";
}
catch (err) {
    result = "Failed: Code: " + err.code + "\n  State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt;
}
return result;
$$
;

经测试，stmt2的查询结果可正常返回CHANGE_MRR_EVENT_TYPE、PRODUCT_NAME等字段，但INSERT操作始终未执行且无任何报错，怀疑是变量绑定方式存在问题，恳请提供排查方向。

排查方向 #

• 条件判断逻辑失效：重点检查if (CHANGE_MRR_EVENT_TYPE === 'Activation' && PRODUCT_NAME === single_quote+Product_In+single_quote)。若原表product_name字段本身不含单引号，拼接了单引号的Product_In永远无法匹配，导致INSERT逻辑根本没触发。可以在循环内添加调试日志，输出PRODUCT_NAME和single_quote+Product_In+single_quote的实际值，验证是否相等。
• 游标移动逻辑错误：当前仅当if条件成立时才调用resultSet2.next()，若条件不成立，游标不会移动，会重复读取同一行数据，甚至导致循环异常。应将resultSet2.next()移至循环末尾，确保无论条件是否满足，游标都正常推进：

  for (i = 1; i <= resultSet2.getRowCount(); i++) {
      // 读取列值逻辑...
      if (...) {
          // INSERT逻辑...
      }
      // 统一移动游标
      resultSet2.next();
  }
  

• 绑定参数顺序/数量不匹配：
  • 检查stmt2中SQL占位符（:1、:2等）与binds数组[SITE_ID, MODEL_SEQ, PCT, PRODUCT]的顺序、数量是否完全对应；
  • 检查INSERT语句sql_insert的14个占位符，与binds数组的元素数量、顺序是否和forecast表的列顺序一致。
• 事务提交问题：Snowflake存储过程默认自动提交，但如果存在隐性事务未提交，可能导致INSERT未生效。可在INSERT后显式添加snowflake.commit()尝试解决。
• 权限验证：确认执行存储过程的角色拥有forecast表的INSERT权限，避免因权限不足静默失败。
• 添加调试日志：在关键节点（如进入if块、执行INSERT前后）添加日志插入语句，记录变量值和执行状态，定位代码是否执行到INSERT环节。

内容的提问来源于stack exchange，提问作者C B