Snowflake存储过程如何实现类似SQL Server的BREAK/RAISERROR退出抛错效果

你现有代码的问题是每个独立try/catch块捕获错误后仅写入了日志，没有主动终止流程，所以后续步骤会继续执行。只需要在每个步骤的catch块完成日志写入后主动抛出错误，即可中断整个存储过程的运行。

修改后的完整代码如下：

CREATE OR REPLACE PROCEDURE DIM_TABLES_REFRESH() 
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    AS
    $$      
        var DB_SCHEMA = 'DB_DEV.DMS';
        var exec_status = 'Success'; // 统一声明状态变量，避免重复定义
        
        /*****  第一组：刷新DIM表 DIM_ACT_REVCODE_HIER ****/         
        try {      
            var truncate_1 = `TRUNCATE TABLE ${DB_SCHEMA}.DIM_ACT_REVCODE`;
            snowflake.execute({sqlText: truncate_1});
            
            var populate_1 = `INSERT INTO ${DB_SCHEMA}.DIM_ACT_REVCODE
                            (
                             ACT_REVCODE_HIER_KEY 
                            ,REV_CODE
                            ,REV_CODE_DESC
                            )
                        SELECT
                             ROW_NUMBER() OVER ( ORDER BY REV_CODE ) AS ACT_REVCODE_HIER_KEY
                            ,REV_CODE
                            ,REV_CODE_DESC                          
                        FROM ${DB_SCHEMA}.OBIEE_ACT_REVCODE`;
            snowflake.execute({sqlText: populate_1});
            
            var insert_status_sp1 = `INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),1,'ACT_REVCODE','Success','');`
            var exec_sp1_status = snowflake.createStatement({sqlText: insert_status_sp1}).execute();
            exec_sp1_status.next();
        }
        catch (err) {
            exec_status = err;
            var insert_status_sp1 = `INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),1,'ACT_REVCODE','Failed',:1);`
            var exec_sp1_status = snowflake.createStatement({sqlText: insert_status_sp1,binds:[err.message]}).execute();
            exec_sp1_status.next();
            // 写完日志直接抛出错误，终止整个存储过程
            throw `步骤1执行失败：${err.message}`;
        }   
        
        /*****  第二组：刷新DIM表 ACTIVITY_HIER ****/         
        try {      
            var truncate_2 = `TRUNCATE TABLE ${DB_SCHEMA}.ACTIVITY_HIER`;
            snowflake.execute({sqlText: truncate_2});
            
            var populate_2 = `INSERT INTO ${DB_SCHEMA}.ACTIVITY_HIER
                            (
                             ACTIVITY_HIER_KEY
                            ,ACTIVITY_CD
                            ,ACTIVITY_DESC
                            )
                        SELECT
                             ROW_NUMBER() OVER ( ORDER BY ACTIVITY_CD ,ORG_CODE ) AS ACTIVITY_HIER_KEY
                            ,ACTIVITY_CD
                            ,ACTIVITY_DESC
                        FROM ${DB_SCHEMA}.OBIEE_ACTIVITY_HIER`;
            snowflake.execute({sqlText: populate_2});
            
            var insert_status_sp2 = `INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),2,'ACTIVITY_HIER','Success','');`
            var exec_sp2_status = snowflake.createStatement({sqlText: insert_status_sp2}).execute();
            exec_sp2_status.next();
        }
        catch (err) {
            exec_status = err;
            var insert_status_sp2 = `INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),2,'ACTIVITY_HIER','Failed',:1);`
            var exec_sp2_status = snowflake.createStatement({sqlText: insert_status_sp2,binds:[err.message]}).execute();
            exec_sp2_status.next();
            // 写完日志直接抛出错误，终止整个存储过程
            throw `步骤2执行失败：${err.message}`;
        }

        return '所有步骤执行成功';
    $$;
CALL DIM_TABLES_REFRESH() ;

主要调整说明 #

• 移除了每个try块内重复的exec_status变量定义，统一在顶部声明避免作用域混乱
• 移除了SQL模板中冗余的binds参数，你已经用模板字符串把${DB_SCHEMA}嵌入到SQL里了，不需要再传binds参数
• 修正了第二组INSERT语句里重复写ACTIVITY_CD字段的语法错误
• 每个catch块写完错误日志后新增throw语句，直接抛出错误终止整个存储过程，后续步骤不会再执行
• 存储过程会直接返回对应步骤的错误信息，同时错误日志已经正常写入日志表

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