Snowflake存储过程插入表报错 需实现ID存在则更新逻辑

错误根因 #

• SQL语法错误：原有INSERT语句混淆了INSERT和UPDATE的语法规则，错误地在列定义位置直接给列赋值（"TBL_CallBack_Parmeter" = :1这类写法属于UPDATE的SET子句语法，不能用在INSERT的列声明段），SQL解析阶段直接失败，抛出的date_value is not defined属于解析阶段的误导性报错。
• 日期类型处理错误：Snowflake JavaScript存储过程中，传入的DATE类型参数是平台自定义的日期对象，并非JavaScript原生Date实例，直接调用.toISOString()方法会触发类型错误，该类型参数无需手动格式转换，直接绑定即可由驱动自动适配。
• 业务逻辑缺失：原有逻辑仅实现了插入操作，未覆盖「ID已存在时更新记录」的需求。

修复后完整代码 #

采用Snowflake原生MERGE语句实现upsert逻辑（匹配ID则更新、不匹配则插入），避免先查后写带来的并发冲突与性能损耗，同时修正语法与类型处理问题：

CREATE OR REPLACE PROCEDURE "CALLBACK_UPDATE1"
("CALLBACK_PARAMETER" VARCHAR(200),
"CALLBACK_IDS" VARCHAR(100),
"SELEKTION" VARCHAR(50),
"SUB_SELEKTION" VARCHAR(50),
"TEXT_INPUT" VARCHAR(16384),
"DATE_INPUT" DATE,
"INAKTIV" VARCHAR(3),
"GID" VARCHAR(20))
RETURNS String not null
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS 
$$
var command = `
MERGE INTO "T_CALLBACK" t
USING (SELECT :2 AS "TBL_CallBack_ID") s
ON t."TBL_CallBack_ID" = s."TBL_CallBack_ID"
WHEN MATCHED THEN
    UPDATE SET
        "TBL_CallBack_Parmeter" = :1,
        "TBL_CallBack_Selektion" = :3,
        "TBL_CallBack_SubSelektion" = :4,
        "TBL_CallBack_Text" = :5,
        "TBL_CallBack_Date" = :6,
        "TBL_CallBack_Inaktiv" = :7,
        "TBL_CallBack_Username" = :8,
        "TBL_CallBack_Timestamp" = current_timestamp()
WHEN NOT MATCHED THEN
    INSERT (
        "TBL_CallBack_Parmeter",
        "TBL_CallBack_ID",
        "TBL_CallBack_Selektion",
        "TBL_CallBack_SubSelektion",
        "TBL_CallBack_Text",
        "TBL_CallBack_Date",
        "TBL_CallBack_Inaktiv",
        "TBL_CallBack_Username",
        "TBL_CallBack_Timestamp"
    )
    VALUES (
        :1, :2, :3, :4, :5, :6, :7, :8, current_timestamp()
    )
`;

try {
    var cmd_dict = {
        sqlText: command, 
        binds: [
            CALLBACK_PARAMETER,
            CALLBACK_IDS,
            SELEKTION, 
            SUB_SELEKTION, 
            TEXT_INPUT, 
            DATE_INPUT, 
            INAKTIV, 
            GID
        ]
    };
    var stmt = snowflake.createStatement(cmd_dict);
    var res = stmt.execute();
    res.next();
    return 'success, affected rows: ' + res.getColumnValue(1);
} catch (err) {
    return "Procedure_Failed: Code: " + err.code + "\n Message: " + err.message + "\n Stack Trace:" + err.stackTraceTxt;
}
$$;

调用方式 #

原有调用语句无需修改即可直接执行：

call "CALLBACK_UPDATE1"
('Param1','P123CCN2','Active','Test','testing procedure','2022-01-01','T','ZOO4');

首次传入新ID时会执行插入，重复传入相同ID时会自动更新对应记录的字段值与更新时间戳，完全符合业务需求。

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