Logging a procedure information into a table in snowflake

user_16879173 0
Viewed 258

I have a table that logs certain information about procedures that are called PROCEDURE_NAME, PARAMETERS_PASSED, START_TIME,END_TIME, ERROR_MESSAGE ... and others

I am creating a procedure which inserts into this table the information I want. However, I do not know how I am to pass the error message from the stored procedure that is called into my new procedure

Is there a way to save this error message

EX)

SP1 - STARTS

SP1 - ENDS & THERE IS AN ERROR

LOG_STORED_PROCEDURE(SP1_NAME,SP1_PARAMETERS,SP1_START_TIME,SP1_END_TIME,ERROR_MESSAGE)

snowflake-cloud-data-platform
1 Answers

Use JavaScript's try/catch block and do the insert into an error table in the catch block. Here's a sample you can modify:

create or replace procedure catch_error_example()
returns string
language javascript
execute as caller
as
$$


    // Try to insert into a non-existent table.

    try{
        cmd = {sqlText: "insert into NON_EXISTENT_TABLE select 1"};
        stmt = snowflake.createStatement(cmd);
        stmt.execute();
    } catch (e) {
        cmd = {sqlText: `insert into ERROR_LOG (STORED_PROC, ERROR_MESSAGE) values ('CATCH_ERROR_EXAMPLE', '${escapeString(e.message)}')`};
        stmt = snowflake.createStatement(cmd);
        stmt.execute();
    }
   
return "Check error log.";
   
function escapeString(value) {
    var s = value.replace(/\\/g, "\\\\");
    s = s.replace(/'/g, "''" );
    s = s.replace(/"/g, '\\"');
    s = s.replace(/\s+/g, " ");
    return s;
}

$$;

create temp table ERROR_LOG(ERROR_TIME timestamp default current_timestamp, STORED_PROC string, ERROR_MESSAGE string);

call catch_error_example();

select * from ERROR_LOG;
user_127563810
Related