Implementing error handling in Snowflake using a Try Catch block has been my focus. I've enclosed SQL queries within JavaScript for this purpose. However, upon executing the query, I noticed that it skips the Try Catch block and directly executes the return statement without running any of the queries inside.
CREATE OR REPLACE PROCEDURE "SP_N_1Test"("STAGE_S3" VARCHAR(16777216), "STAGE_OUTPUT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE Javascript
EXECUTE AS CALLER
AS
$$
var cmd = `truncate table STAGE2A.T001_IRF_STUDENT_FORM_S3`;
var my_sql_command1 = snowflake.createStatement({sqlText: cmd});
var result = my_sql_command1.execute();
var cmd1 = `''COPY INTO STAGE2A.T001_IRF_STUDENT_FORM_S3
FROM ( select
FN_TrimStr($1) as State,
FN_TrimStr($2) as AdminCode,
from @stage2a.''||:STAGE_S3||'')
pattern= ''''.*_IRF_.*\\\\.csv''''
file_format = (type=csv, skip_header=1 )''`;
var my_sql_command2 = snowflake.createStatement({sqlText: cmd1});
var result1 = my_sql_command2.execute();
var cmd2 = `Insert into STAGE2B.T011_IRF_STUDENT_FORM_V001 (
STATE_FLAG,
STATE_CD,
)
SELECT
STATE_FLAG,
STATE_CD,
from STAGE2A.V001_IRF_STUDENT_FORM_T001`;
var my_sql_command3 = snowflake.createStatement({sqlText: cmd2});
var result2 = my_sql_command3.execute();
return;
$$;
In addition to this issue, I have also tried to include a message in each Try block to indicate success when it runs successfully. However, attempts with
Print, Println, sustem.print.out()