Search code examples

Snowflake JavaScript Stored Procedure Returns Success Despite Expected Failure

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
      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();


| 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!


  • 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";


    call actual_failure();

    actually fails.

    enter image description here