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);