How to add error handling using SQL in snowflake
I've written below stored procedure code using SQL in snowflake which truncates a table and load new data into that table by first copying the data from a source, applying a bit of processing on it and loading into the target table which we truncated.
I've added nested Begin and End statements in order to try to add error handling functionality along with If Else statements but none of them worked. I want to first test if copy data was successful if yes than code should run second insert statements which basically brings data to staging where we refine the data where I want to add second check which checks if the rows were added successfully. Lastly we copy into the target table after all the checks are passed.
CREATE OR REPLACE PROCEDURE DEV_NMC_ITEM_AND_PSYCHOMETRIC_DM.STAGE2B."SP_N_1Test"("STAGE_S3" VARCHAR(16777216), "STAGE_OUTPUT" VARCHAR(16777216)) RETURNS VARCHAR(16777216) LANGUAGE SQL EXECUTE AS CALLER AS ' DECLARE Stage_nm_s3 STRING; begin truncate table "STAGE2A"."T001_IRF_STUDENT_FORM_S3"; execute immediate ''COPY INTO "STAGE2A"."T001_IRF_STUDENT_FORM_S3" FROM ( select a bunch of columns from @stage2a.''||:STAGE_S3||'') pattern= ''''.*_IRF_.*\\\\.csv'''' file_format = (type=csv, skip_header=1 )''; begin Insert into "STAGE2B"."T011_IRF_STUDENT_FORM_V001" ( a bunch of columns SELECT a bunch of columns from "STAGE2A"."V001_IRF_STUDENT_FORM_T001"; begin execute immediate ''copy into @stage2a.''||:STAGE_OUTPUT||''/T001_IRF_STUDENT_FORM_S3 from (SELECT a bunch of columns from "STAGE2B"."T011_IRF_STUDENT_FORM_V001") file_format = ( format_name = F_CSV type=csv compression = none) header = True SINGLE = FALSE OVERWRITE = TRUE max_file_size=524288000 ''; return ''Load process completed for IRF_STUDENT_FORM_S3''; end; end; end; ';```
Here's some more explanation on that topic: Error handling for stored procedures