Snowflake JavaScript存储过程报Unexpected token ':'语法错误求助

报错根因 #

• 参数大小写不匹配：Snowflake JS存储过程的输入参数在代码块内必须全大写使用，你定义的app_name、err_msg、misc_str三个入参，调用时需写为APP_NAME、ERR_MSG、MISC_STR，原代码插入语句的绑定参数错误使用了sqlCmd变量替代MISC_STR。
• 严格模式下变量未声明：你开启了strict严格模式，代码中stmt、result、error_msg等变量未使用var声明，直接赋值会触发语法错误。
• 返回值缺失：result变量仅在异常捕获分支赋值，执行成功时返回未定义值，不符合存储过程returns string not null的约束。

修正后完整可运行代码 #

create or replace sequence SEQ_ERR;
create or replace table ERROR_LOG(ERR_SEQ NUMBER, APP_NAME VARCHAR(250), ERR_MSG VARCHAR(2500), MISC_STRING VARCHAR(2500), err_date timestamp);

CREATE OR REPLACE PROCEDURE ERR_LOGGING(app_name varchar, err_msg varchar, misc_str varchar )
returns string not null
language javascript
strict volatile
execute as caller
as
$$
var result = "";
try {
    var v_time_st = getScalar("SELECT CURRENT_TIMESTAMP");
    var v_err_seq = getScalar("SELECT SEQ_ERR.nextval");    
    var error_msg = 'This is in the line before the execute';
    var sqlCmd = `insert into ERROR_LOG (ERR_SEQ , APP_NAME, ERR_MSG , MISC_STRING, ERR_DATE) VALUES (:1, :2, :3, :4, :5)`;
    var stmt = snowflake.execute({ sqlText: sqlCmd, binds: [v_err_seq, APP_NAME, ERR_MSG, MISC_STR, v_time_st]});
    result = "执行成功，错误日志已写入";
}
catch (err)  {
    result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt;
}  
function getScalar(queryString) {
    var cmd1 = {sqlText: queryString};
    var stmt = snowflake.createStatement(cmd1);
    var rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(1);
}

return result;
$$;

call ERR_LOGGING('TEST_APP', 'This is an error messge', 'ON line 1');

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