I’ve recently been experimenting with Snowflake’s JavaScript stored procedures and encountered an unexpected behavior. I created a simple stored procedure as outlined in the Snowflake documentation here.

Here’s the stored procedure I created:

create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

The purpose of this stored procedure is to deliberately fail by executing an invalid SQL command and then catch the error using a try-catch block.

When I call the stored procedure, it indeed returns the expected error message, indicating a failure:

call broken();

Output:

+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 1003                                      |
|   State: 42000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| Snowflake.execute, line 4 position 20                   |
+---------------------------------------------------------+

However, upon checking Snowflake’s query history, I noticed that this procedure is marked as a successful run. This seems contradictory since the procedure explicitly aims to fail. I expect it to be tagged as a failed execution.

Could anyone shed some light on why Snowflake considers this a successful run despite the expected failure? Is there a different approach to handle such scenarios and ensure that failed stored procedure executions are correctly reflected in the query history? Any insights or suggestions would be greatly appreciated. Thank you!

>Solution :

return a string "I HAVE FAILED" is a success, because it happened, where-as, actually failing:

create procedure actual_failure()
    returns varchar not null
    language javascript as
$$
    snowflake.execute( {sqlText: "Invalid Command!;"} );
    return "Succeeded";
$$
;

then

call actual_failure();

actually fails.