How to add error handling using SQL in snowflake-CodePudding

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;
';```

CodePudding user response：

I'm afraid you will need to wrap up your SQL statements into Javascript-syntax stored procedure to use Try/Catch block.

Here's some more explanation on that topic: Error handling for stored procedures