Error handling in a Snowflake Stored Procedure

Introduction

One of the things you should look at when you are building stored procedures in Snowflake is error handling. In Snowflake there is the option of using EXCEPTION in code blocks. An exception is a (Oxford Languages):  "person or thing that is excluded from a general statement or does not follow a rule.". Something happens in your program that isn't expected. 

Currently, Snowflake support three kinds of exceptions :

• STATEMENT_ERROR. This exception indicates an error while executing a statement. 
• EXPRESSION_ERROR. This exception indicates an error related to an expression.
• Other Exceptions. Accroding to Snowflake : "To handle all other exceptions that do not have a WHEN clause, use a WHEN OTHER THEN clause.". But this is not the complete story in my opinion as I'm concluding in this blogpost.

Now, what is not clear to me yet is whether I should implement, them all three or can I implement only one? For instance, If I don't implement EXPRESSION_ERROR, but I have implemented OTHER, can I conclude that an expression error will be handled by the other exception?

How exceptions work

To understand how exceptions work in Snowflake, I've conducted a couple of experiments.

Experiment 1 Statement error

 So the first one is a statement error, by simulating a division by zero.

DECLARE
     ERRORMSG STRING;
BEGIN

    SELECT 1/0; 
    
    EXCEPTION
        WHEN STATEMENT_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS A STATEMENT ERROR');
        WHEN EXPRESSION_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN EXPRESSION ERROR');
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

This results in a statement error :

Experiment 2 Other error

What happens when I remove the STATEMENT_ERROR exception from the code ?

DECLARE
     ERRORMSG STRING;
BEGIN

    SELECT 1/0; 
    
    EXCEPTION
        -- WHEN STATEMENT_ERROR THEN
        --     ERRORMSG := SQLERRM;
        --     RETURN ('THIS IS A STATEMENT ERROR');
        WHEN EXPRESSION_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN EXPRESSION ERROR');
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

And this results in : 

And now it is an 'other' error handled by the 'other' exception. It seems that the 'other' exception is like an ELSE in IF statement. If nothing is true then it's an else.

Experiment 3 Expression error

What if I create an expression error? For instance assigning a string at a float? 

DECLARE
     ERRORMSG STRING;
     STR_VALUE STRING := 'STRING';
     FL_VALUE FLOAT;
BEGIN
    --SELECT 1/0; 
    
    FL_VALUE := STR_VALUE;
    
    EXCEPTION
        WHEN STATEMENT_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS A STATEMENT ERROR');
        WHEN EXPRESSION_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN EXPRESSION ERROR');
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

And this results in :

Experiment 4 Other error

What happens when I remove the EXPRESSION exception?

DECLARE
     ERRORMSG STRING;
     STR_VALUE STRING := 'STRING';
     FL_VALUE FLOAT;
BEGIN
    --SELECT 1/0; 
    
    FL_VALUE := STR_VALUE;
    
    EXCEPTION
        WHEN STATEMENT_ERROR THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS A STATEMENT ERROR');
        -- WHEN EXPRESSION_ERROR THEN
        --     ERRORMSG := SQLERRM;
        --     RETURN ('THIS IS AN EXPRESSION ERROR');
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

And this results in :

And now it becomes an OTHER error.

Experiment 5 Other error

What if I remove the 'statement' exception and I have only the other error? What happens then?

DECLARE
     ERRORMSG STRING;
     STR_VALUE STRING := 'STRING';
     FL_VALUE FLOAT;
BEGIN
    --SELECT 1/0; 
    
    FL_VALUE := STR_VALUE;
    
    EXCEPTION
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

And this results in :

And this works. So it seems to me that the 'other' exception are ALL the exceptions that can happen as my experiments are indicating.

Experiment 4 Other Error

Just to be sure if it also works for a statement error too. Uncomment the SELECT 1/0 again and then see what happens

DECLARE
     ERRORMSG STRING;
     STR_VALUE STRING := 'STRING';
     FL_VALUE FLOAT;
BEGIN
    SELECT 1/0; 
    
    --FL_VALUE := STR_VALUE;
    
    EXCEPTION
        WHEN OTHER THEN
            ERRORMSG := SQLERRM;
            RETURN ('THIS IS AN OTHER ERROR');
END;

And this results in :

Final Thoughts

For the purpose I currently have, I just want to monitor certain loading procedures. For this reason, I only have to implement the OTHER exception. The other (other ;-)) exceptions (STATEMENT, EXPRESSION) can useful if you want certain specific error handling.

Hennie