Error handling block not executing in snowflake using SQL

Question

I'm trying to implement error handling in snowflake using Try Catch block. Enclosed SQL queries in javascript for applying error handling. When I execute the query it executes return statement directly and none of the queries inside Try Catch block runs.

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

Also, I want to print a message in each Try block to output success when it runs successfully. I've used Print, Println, sustem.print.out() but none of them worked.

Answer 1

It seems you have some confusion about calling SQL statements inside the procedure. You need to use snowflake API to call SQLs:

https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html

What I see here is:

try{ `truncate table "STAGE2A"."T001_IRF_STUDENT_FORM_S3"`;} -- nothing to execute
catch (err){}
try{
`bla bla bla bla`; --- nothing to execute
return "Load process completed for IRF_STUDENT_FORM_S3"; 
}
catch(err){}

You opened backtick (`) and closed it before the return command, making everything template string. This is why you see the function returns immediately. As I said, you need to check how to use snowflake API to call the SQLs.

About writing an output, you can't write an output like this but you can define a variable and return it, or you can insert the logs to a table.

CREATE OR REPLACE PROCEDURE sp_test("STAGE_S3" VARCHAR(16777216), "STAGE_OUTPUT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE Javascript
EXECUTE AS CALLER
AS 
'
var result = "";

try{ `truncate table "STAGE2A"."T001_IRF_STUDENT_FORM_S3"`;
   result += "table truncated ";
}
catch (err){}
...
result += "Load process completed for IRF_STUDENT_FORM_S3";

}

catch(err){}

return result; 
';