Exit the stored procedure and RAISE error in Snowflake stored procedure like BREAK or RAISERROR in SQL Server

Question

I have a stored procedure which executes multiple queries and for each of those queries I have a try and catch block as shown below. If there is an error, I capture and insert into a LOG table.

However, I also need to BREAK and come out of that step, wherever error is encountered. as an e.g. if I have error in the first try / catch it shouldn't execute the second query and exit with ERROR.

code:

CREATE OR REPLACE PROCEDURE DIM_TABLES_REFRESH() 
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    AS
    $$      
        var DB_SCHEMA = 'DB_DEV.DMS';
        
       
try {      
         /*****  Refresh DIM table  DIM_ACT_REVCODE_HIER ****/         
         var truncate_1 =` TRUNCATE TABLE   ${DB_SCHEMA}.DIM_ACT_REVCODE    `;
         snowflake.execute({sqlText: truncate_1, binds: [DB_SCHEMA]});
         
         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, binds: [DB_SCHEMA]});
         
         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();
            
         var exec_status='Success';
        }
        catch (err) {
        exec_status =err;
        if (exec_status!='Success') {
            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();
                                    } 
        }   
        

try {      
         /*****  Refresh DIM table  ACTIVITY_HIER ****/         
         var truncate_2 =` TRUNCATE TABLE   ${DB_SCHEMA}.ACTIVITY_HIER  `;
         snowflake.execute({sqlText: truncate_2, binds: [DB_SCHEMA]});
         
         var populate_2 = `INSERT INTO ${DB_SCHEMA}.ACTIVITY_HIER
                            (
                             ACTIVITY_HIER_KEY
                            ,ACTIVITY_CD
                            ,ACTIVITY_DESC
                            ,ACTIVITY_CD
                              
                            )
                        SELECT
                             ROW_NUMBER() OVER ( ORDER BY ACTIVITY_CD ,ORG_CODE ) AS ACTIVITY_HIER_KEY
                            ,ACTIVITY_CD
                            ,ACTIVITY_DESC
                            ,ACTIVITY_CD
                            
                        FROM ${DB_SCHEMA}.OBIEE_ACTIVITY_HIER`;
         snowflake.execute({sqlText: populate_2, binds: [DB_SCHEMA]});
         
         var insert_status_sp1=`INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),2,'ACTIVITY_HIER','Success','');`
            var exec_sp1_status = snowflake.createStatement({sqlText: insert_status_sp1}).execute();
            exec_sp1_status.next();
            
         var exec_status='Success';
        }
        catch (err) {
        exec_status =err;
        if (exec_status!='Success') {
            var insert_status_sp1=`INSERT INTO STATS_QUERY_LOAD_STATUS_LOG values (Current_TIMESTAMP(),2,'ACTIVITY_HIER','Failed',:1);`
            var exec_sp1_status = snowflake.createStatement({sqlText: insert_status_sp1,binds:[err.message]}).execute();
            exec_sp1_status.next();
                                    } 
        }

  return 'Success';
    $$;
CALL DIM_TABLES_REFRESH() ;             

Answer 1

we can use THROW statement as shown below to stop and exit by reporting an error.

e.g. THROW ERROR;