I am trying to use a stored procedure to insert to a log table (along with some other functionality that isn't relevant). This procedure needs to be called automatically by a task, execute some code, then write to a table. I can do all of the above, however when I include a try/catch block for error handling the procedure wont update the log tables.

Minimal working example:

Creating the log table and procedure to write to it,

create or replace table test_log_table (
  PROC_NAME VARCHAR, 
  MESSAGE VARCHAR,
  TIME TIMESTAMP
   );

CREATE OR REPLACE PROCEDURE LOG_SP_TEST(TRGT_TABLE VARCHAR, MESSAGE VARCHAR)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$  
    const PROC_NAME = Object.keys(this)[0]; // name of executing procedure
    
    insert_sql =
    `INSERT INTO `   TRGT_TABLE `
    (
      PROC_NAME, 
      MESSAGE,
      TIME
    )
    VALUES
    ('`  PROC_NAME  `','` MESSAGE `', CURRENT_TIMESTAMP());` 
    
    try{col_list_fetch_trgt = snowflake.execute(
        { sqlText: insert_sql })
    }
    catch(err){
        return "failed: " err
    }
$$;

When calling the procedure manually it all behaves as expected

call log_sp_test('test_log_table', 'calling manually');
select * from test_log_table;

The select * returns the following table

Now we create a task and call the procedure again automatically

-- now test calling this proc using a task
create task mytask_minute
  warehouse = COMPUTE_WH
  schedule = '1 minute'
as
  call log_sp_test('test_log_table', 'calling via trigger');
  
alter task mytask_minute resume;
show tasks;

-- can then check when a task is next due to run by
select timestampdiff(second, current_timestamp, scheduled_time) as next_run, scheduled_time, current_timestamp, name, state 
from table(information_schema.task_history()) where state = 'SCHEDULED' order by completed_time desc;

select * from test_log_table;
alter task mytask_minute suspend;
show tasks;

The select * statement still returns the same

Whilst if we remove the try/catch block in the procedure we get the expected

How can I call this procedure automatically whilst still being able to write to a log table and properly handle errors?

CodePudding user response：

Changes:

• using ; at end of the line
• using parameters instead of concatenation
• calling procedure with schema name 'PUBLIC.test_log_table'

Procedure:

CREATE OR REPLACE PROCEDURE LOG_SP_TEST(TRGT_TABLE VARCHAR, MESSAGE VARCHAR)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$  
    const PROC_NAME = Object.keys(this)[0]; // name of executing procedure
    
    insert_sql = `INSERT INTO IDENTIFIER(?) ( PROC_NAME,  MESSAGE, TIME)
                 VALUES(?, ?, CURRENT_TIMESTAMP());`;
    
    try{
      col_list_fetch_trgt = snowflake.execute(
        { sqlText: insert_sql, binds:[TRGT_TABLE, PROC_NAME, MESSAGE] });
    }
    catch(err){
        return "failed: " err
    }
$$;

Task:

create OR REPLACE task mytask_minute
  warehouse = COMPUTE_WH
  schedule = '1 minute'
as
  call PUBLIC.log_sp_test('PUBLIC.test_log_table', 'calling via trigger');

Output: