I am trying Try and Catch Error handling for snowflake stored procedure. in the Stored procedure there are four different statements s

user_5459718 0
Viewed 700

I am trying to do error handling for snowflake stored procedure. The stored procedure has 4 SQL statement inside. I need check errors for all for statement. When error pops, i need to know which Statement error. How to achieve this? Thanks in advance. I appreciate it.

 CREATE OR REPLACE PROCEDURE dw.sp_fidctp_audit_trail_load(SYSDATE varchar)
    RETURNS STRING
    LANGUAGE javascript
    AS
    
    ' 
    
    
      var sql_command = "truncate table STG.tb_fidctp_audit_trail_input";
      var result =  snowflake.execute ( {sqlText: sql_command, binds: [SYSDATE]});
      
        
      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]}
              );
          return "Succeeded.";   // Return a success/error indicator.
          }
      catch (err)  {
          return "Failed: " + err + "ERROR ON COPY STATEMENT";   // Return a success/error indicator.
          }
        
        
        sql_command = `delete from DW.tb_fidctp_audit_trail
                 where  SYSDATE = to_date((:1), ''YYYYMMDD'')`;
        result =  snowflake.execute ( {sqlText: sql_command, binds: [SYSDATE]});
      
     
                
        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]}
              );
          return "Succeeded.";   // Return a success/error indicator.
          }
      catch (err)  {
          return "Failed: " + err + "ERROR ON INSERT STATEMENT";   // Return a success/error indicator.
          }
        
      
   
    
   '
    ;
snowflake-cloud-data-platform
2 Answers

There are a few things to do here. Let's start with the create header:

 CREATE OR REPLACE PROCEDURE dw.sp_fidctp_audit_trail_load(SYSDATE varchar)
    RETURNS STRING
    LANGUAGE javascript
    AS
    
    '

When you use a single quote to enclose the body of a JavaScript procedure, it makes using single quotes in SQL statements inside the body very cumbersome, having to double the single quotes. To avoid that, you can use an alternate string terminator in Snowflake, $$ like this:

CREATE OR REPLACE PROCEDURE dw.sp_fidctp_audit_trail_load(SYSDATE varchar)
  RETURNS STRING
  LANGUAGE javascript
  AS
$$
  // Body goes here. There's no longer a need to escape single quotes
$$;

The issue with the return values is in this part:

      return "Succeeded.";   // Return a success/error indicator.
      }
  catch (err)  {
      return "Failed: " + err + "ERROR ON COPY STATEMENT";   // Return a success/error indicator.
      }

In JavaScript, when the return statement is used in the main function, it leaves the main function, which terminates the stored procedure. This means if the copy statement succeeds, the procedure will exit and return "Succeeded." If the copy statement fails, it will hit a different return statement and exit with the error message.

Either way, it will terminate execution after that code block.

Take the return statements out of the successful branches of the try/catch blocks except for the last one.

user_127563810

You can just print the statement in the CATCH statement, either by printing the sql_command variable, or using getSqlText() function from the Statement object.

CREATE OR REPLACE PROCEDURE test_catch_error_sp(SYSDATE varchar)
  RETURNS STRING
  LANGUAGE javascript
  AS
$$
    var commands = [];
    var params = [];
    var results = {"success": [], "failed": []};

    commands[0] = "create or replace table test1 (a int)";
    params[0] = [];
    
    commands[1] = "INSERT INTO test1 values (?)";
    params[1] = [SYSDATE];
    
    commands[2] = "drop table test1";
    params[2] = [];
    
    var stmt = null;

    for (i = 0; i < commands.length; i++) {
      try {
        stmt = snowflake.createStatement({sqlText: commands[i], binds: params[i]});
        var result = stmt.execute();
        
        results['success'].push(stmt.getSqlText());

        stmt = null; // reset it
      } catch (err) {
        // if createStatement failed, then stmt will still be null
        results['failed'].push(
          "Failed: " + err + ". ERROR ON STATEMENT: '" + stmt ? stmt.getSqlText() : commands[i] + "'"
        );
      }
    }
    
    return JSON.stringify(results);
$$
;

call test_catch_error_sp('2020-01-01');
-- returns:
-- {"success":["create or replace table test1 (a int)","drop table test1"],"failed":["INSERT INTO test1 values (?)"]}
user_98793790
Related