如何实现Snowflake存储过程中多SQL语句的错误定位与异常捕获

你当前代码存在逻辑错误：COPY语句执行成功后会直接return，后续的DELETE、INSERT语句永远不会被执行，需要先修复该问题，再给每一条SQL配置独立的异常捕获逻辑即可实现精准定位。

以下是修正后的完整存储过程代码：

CREATE OR REPLACE PROCEDURE dw.sp_fidctp_audit_trail_load(SYSDATE varchar)
RETURNS STRING
LANGUAGE javascript
AS
$$
// 第1条SQL：清空stg层输入表
var sql_command = "truncate table STG.tb_fidctp_audit_trail_input";
try {
    var result = snowflake.execute({sqlText: sql_command, binds: [SYSDATE]});
} catch (err) {
    return "Failed: " + err + " | 出错语句：TRUNCATE清空STG输入表";
}

// 第2条SQL：COPY从stage加载数据到stg表
sql_command = `COPY INTO STG.tb_fidctp_audit_trail_input (record_id,ORDER_ID,PARENT_ORDER_ID,EVENT_TYPE,EVENT_DATETIME,PRIMARY_STATE,SECONDARY_STATE,CURRENT_SERVICE_ID,CURRENT_EXECUTOR_ID,TRADING_QUANTITY,LIMIT_PRICE,EVENT_TEXT,ORDER_NOTES,REASON_TEXT,ROUTED_ORDER_CODE,ROOT_ORDER_ID,INSTRUMENT_CODE,ORDER_SOURCE,LEAVES,ACCT_ID,CUST_CROSS_ID,ORDER_FLAGS)
from @STG.CTP_STAGE/AUDIT_TRAIL.TOP.`;
sql_command += SYSDATE;
sql_command += ".psv.gz file_format = (FORMAT_NAME = 'STG.CTP_AUDIT_TRAIL_FF', ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE, encoding = 'iso-8859-1') ";
try {
    result = snowflake.execute({sqlText: sql_command, binds: [SYSDATE]});
} catch (err) {
    return "Failed: " + err + " | 出错语句：COPY加载 staged 文件到STG表";
}

// 第3条SQL：删除dw层目标表当日数据
sql_command = `delete from DW.tb_fidctp_audit_trail
where  SYSDATE = to_date((:1), 'YYYYMMDD')`;
try {
    result = snowflake.execute({sqlText: sql_command, binds: [SYSDATE]});
} catch (err) {
    return "Failed: " + err + " | 出错语句：DELETE删除DW表当日历史数据";
}

// 第4条SQL：插入stg层数据到dw层目标表
sql_command = ` insert into DW.tb_fidctp_audit_trail(sysdate, order_id, parent_order_id, root_order_id, event_datetime, instrument_code, event_text,reason_text, current_service_id, current_executor_id, trading_quantity, leaves )
select  to_date(left(event_datetime, 8), 'YYYYMMDD'), order_id, parent_order_id, root_order_id, to_timestamp(left(event_datetime, 24), 'YYYYMMDD HH24:MI:SS.FF'), instrument_code, event_text, reason_text, current_service_id, current_executor_id, cast(trading_quantity as NUMBER(18,0)), cast(leaves as NUMBER(18,0))
from  STG.tb_fidctp_audit_trail_input
`;
try {
    snowflake.execute({sqlText: sql_command, binds: [SYSDATE]});
} catch (err) {
    return "Failed: " + err + " | 出错语句：INSERT插入数据到DW目标表";
}

// 所有语句执行成功才返回成功
return "All 4 SQL statements executed successfully.";
$$;

核心调整说明 #

• 移除了原有代码中COPY、INSERT执行成功后的提前return逻辑，保证4条SQL按顺序完整执行，全部成功后才返回最终成功提示
• 每一条SQL都包裹了独立的try-catch块，每个catch块都标注了唯一的语句标识，报错时可直接定位到具体失败的SQL
• 保留了原有业务逻辑和参数绑定规则，不影响原有执行效果
• 统一使用$$包裹存储过程JS代码，避免多层引号转义出错的问题

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