4
0
The error handling in Snowflake Stored procedure (using Javascript) can be implemented using JavaScript Try/Catch. A stored procedure can have multiple Try/Catch blocks to capture the errors or Error Logging produced by multiple sql statements . The finally –block contains statements to execute after the try –block and catch –block(s) execute. If the function exits inside of the try catch block, or another error is thrown in either the try or the catch, the finally will still execute.
TRY/CATCH/FINALLY:
Consider the scenario where you have multiple Insert statements inside the stored procedure block and when there is any error in any of the sql statement, Instead of load gets fail , I need to capture the error logging of rows in a table and continue my load though incase of any errors occur. At the completion of stored procedure all the error logs should be compile or capture at one place. Later on all this should be ingest into a error log table.
In order to achieve this, we have used the FINALLY block inside the procedure where we are ingesting complete error into a Log table.
A finally block is always executed no matter what happens inside your try block. Inside this block we are inserting the compiled log results into a Error log table.
Once we get the combined error into the Log table, We are required to unload this table to the local staging area into a file aka LOG file which will be consumed by other team.
Firstly, Error logging table needs to be create which will hold the Erroneous data: i.e. the error generate by each Try/Catch block while performing SQL operations.
create table log_out (log_col string);
Finally, JavaScript procedure implementing Error handling using TRY/CATCH exception clause along with the FINALLY clause.
Output:
