Custom Exception Handling in Snowflake Scripting | Mobilize.Net

Luckily for us, Snowflake supports exception handling in procedures written using Snowflake Scripting. Let’s see it in action.

Suppose I have a store and I want to keep the inventory of products updated. One of the procedures I have created for this removes units of a certain product from the inventory. Here is that procedure written in Snowflake Scripting:

CREATE OR REPLACE PROCEDURE removeProduct (product STRING, numberOfUnits FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        notEnoughError EXCEPTION(-20001, 'Not enough units of this product');
    BEGIN
        LET actualStock INTEGER;
        SELECT stock INTO :actualStock From inventory WHERE productName = :product;
        IF (actualStock < numberOfUnits) THEN
            RAISE notEnoughError;
        ELSE
            UPDATE inventory SET stock = stock - :numberOfUnits WHERE productName = :product;
        END IF;
        EXCEPTION WHEN notEnoughError THEN
        BEGIN
            INSERT INTO logTable VALUES (CURRENT_TIME, :SQLERRM);
        END;
    END;
$$;

This procedure (1) uses the Snowflake declare statement to create an exception with a custom message, (2) checks the current stock for the product, and (3) reduces the stock for the product or raises our custom exception if there were not enough units left. This allows for our exception handler to do the work of registering the exception in a table.

The procedure looks good and does its job, but unfortunately, in the world of exception handling, meaningful exception messages are key. Suppose I want to remove units of five different products, but two of them do not have enough units. This will cause the message “Not enough units of this product” to be registered twice in my log table, but… which products? The first two? The first and last one? Trying to manually determine which two products are being referenced certainly sounds like a chore.

Dynamically Building and Raising Exceptions in Snowflake

Given this shortcoming, we can improve our code by building the custom exception message dynamically. However, you may have noted something from the Snowflake declare exception syntax: the exception message has to be a literal string. But hey! That is not going to stop us from having these sweet custom exception messages built and raised. Take a look at this procedure also written in Snowflake Scripting:

CREATE OR REPLACE PROCEDURE removeProduct (product STRING, numberOfUnits FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        CUSTOM_SQLERRM VARCHAR;
        notEnoughError EXCEPTION;
    BEGIN
        LET errorText VARCHAR(100);
        LET actualStock INTEGER;
        SELECT stock INTO :actualStock From inventory WHERE productName = :product;
        IF (actualStock < numberOfUnits) THEN
            errorText := 'Tried to remove ' || numberOfUnits || ' units of ' || product || ' but only ' || actualStock || ' left in stock';
            CUSTOM_SQLERRM := errorText;
            RAISE notEnoughError;
        ELSE
            UPDATE inventory SET stock = stock - :numberOfUnits WHERE productName = :product;
        END IF;
        EXCEPTION WHEN notEnoughError THEN
        BEGIN
            LET errmsg VARCHAR(200);
            errmsg := IFF(SQLERRM = 'NULL', CUSTOM_SQLERRM, SQLERRM);
            INSERT INTO logTable VALUES (CURRENT_TIME, :errmsg);
        END;
    END;
$$;

With this new version, I get a more precise message from the exception. For example, if I have three phones in my inventory and try to remove five, the message I will get in my log table is “Tried to remove 5 units of phone but only 3 left in stock”. This is a great improvement over our old “Not enough units of this product”. Let’s take a look at how this version works.

Snowflake has a variable named SQLERRM, which is automatically assigned the exception message when an exception occurs. However, it can only be modified by Snowflake. This means the only way of changing it is declaring a static exception message in the exception declaration part. We would rather not do that, so we are creating a CUSTOM_SQLERRM variable instead. We also declare our custom exception with no message whatsoever.

And instead of raising the error right away like we did before, this time we built our nice custom exception message and assigned it to our CUSTOM_SQLERRM variable. Then, we release the exception.

Finally, we get to the exception handler. Here something interesting happens. We assign either our custom error message or the SQLERRM to the final message that will be registered. Now, you may be asking yourself why we check if SQLERRM is “null” instead of just using CUSTOM_ERRM right away? Well, let’s suppose we want to use this exception handler for multiple exception types or even as your default exception handler. By doing it this way, our exception handler is covered against both of those exceptions: those with a static exception message (defined in the exception declaration and stored in SQLERRM when raised) and our dynamically created exception messages (saved in our CUSTOM_ERRM variable before raising them). The “null” check is done that way because when no exception message is specified in the exception declaration, Snowflake assigns SQLERRM with the string  “NULL” instead of null to represent there was no static exception message.

Now that you know how to declare and raise these custom exception messages at runtime, exception handling in Snowflake Scripting should be simpler.

Automatically Migrating Code

Just one last thing before we say goodbye. It probably wasn't too obvious, but the last procedure we were considering was actually migrated using SnowConvert for Teradata. The original procedure was written in Teradata SQL.  

If you don’t believe me, then you should give it a try in BlackDiamond Studio. Here is the source code in Teradata. Use this as the input to test the migration. When you're done, you can deploy the code directly into Snowflake from the built-in IDE.

REPLACE PROCEDURE removeProduct(product VARCHAR(20), numberOfUnits INTEGER)
BEGIN
    DECLARE errorText VARCHAR(100);
    DECLARE actualStock INTEGER;
    DECLARE notEnoughError CONDITION;
    DECLARE EXIT HANDLER FOR notEnoughError
    BEGIN
        DECLARE errmsg VARCHAR(200);
        GET DIAGNOSTICS EXCEPTION 1
            errmsg = MESSAGE_TEXT;
        INSERT INTO logTable VALUES (CURRENT_TIME, errmsg);
    END;
    SELECT stock INTO actualStock From inventory WHERE productName = product;
    IF (actualStock < numberOfUnits) THEN
        SET errorText = 'Tried to remove ' || numberOfUnits || ' units of ' || product || ' but only ' || actualStock || ' left in stock';
        SIGNAL notEnoughError SET MESSAGE_TEXT = errorText;
    ELSE    
        UPDATE inventory SET stock = stock - numberOfUnits WHERE productName = product;
    END IF;
END;

And that's it! Good luck with your exceptions, and goodbye... for now.