Snowflake存储过程多次绑定参数时报Bind variable ? not set错误如何解决

• 你编写的UPDATE SQL语句内一共存在3个?绑定变量占位符，需要按顺序传入对应数量的绑定参数才能完成匹配。
• 调用snowflake.createStatement方法时，你为配置对象重复声明了3次binds属性，JavaScript对象中同名属性会被后定义的内容覆盖，最终生效的binds数组仅包含1个VENDOR参数，和3个占位符的数量要求不匹配，因此触发绑定变量未设置的报错。

仅需保留1个binds属性，在数组内按占位符顺序传入3个VENDOR即可，修正后的完整存储过程代码如下：

create or replace procedure check(vendor varchar)
returns varchar
language javascript
execute as caller
as
$$
var command7=`update test set id_null=( select count(*) from table where lower(trim(source))=? and id is null), 1_null=(select count(*) from table where lower(trim(source))=? and 1 is null), 2_null=(select count(*) from table where lower(trim(source))=? and 2 is null) where vendor=concat('${VENDOR}','ABC')`;
var stmt7=snowflake.createStatement({sqlText: command7, binds: [VENDOR, VENDOR, VENDOR]});
var rs7=stmt7.execute();
$$

如果想要简化代码减少重复传参，也可以调整SQL逻辑，通过CTE复用过滤条件，仅需要1个绑定参数即可完成相同逻辑：

create or replace procedure check(vendor varchar)
returns varchar
language javascript
execute as caller
as
$$
var command7=`
WITH source_stat AS (
    SELECT
        COUNT_IF(id IS NULL) AS id_null_cnt,
        COUNT_IF(1 IS NULL) AS 1_null_cnt,
        COUNT_IF(2 IS NULL) AS 2_null_cnt
    FROM table
    WHERE lower(trim(source)) = ?
)
UPDATE test
SET 
    id_null = (SELECT id_null_cnt FROM source_stat),
    1_null = (SELECT 1_null_cnt FROM source_stat),
    2_null = (SELECT 2_null_cnt FROM source_stat)
WHERE vendor = concat('${VENDOR}', 'ABC')
`;
var stmt7 = snowflake.createStatement({sqlText: command7, binds: [VENDOR]});
var rs7 = stmt7.execute();
$$

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