Snowflake SQL Tutorial 149 - I Caught The Exception And Every Row It Wrote Is Still There

Snowflake SQL · Episode 149

In this post we are going to cover exceptions and RAISE in Snowflake Scripting.

An exception is what a block does when something goes wrong. A handler is the piece you write to catch it. RAISE is how you throw one on purpose, either one of Snowflake's own or one you declared yourself.

Episode 147 ended on a loop that failed half way through and kept every row it had already written. So the question for this post is whether catching the failure changes that. It does not, and that is the part worth staying for.

We will start with the smallest handler there is and build it up step by step. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

Episode 149 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

Let's create some sample data

First let's see where the session is pointing, so we know what everything below is running against.

Query
SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION,
       CURRENT_VERSION()     AS THE_VERSION;
Result1 row · 4 columns
THE_DATABASETHE_SCHEMAANY_OPEN_TRANSACTIONTHE_VERSION
DEMO_DBPUBLICNULL10.35.101

We are in DEMO_DB and the PUBLIC schema, and there is no transaction open. Keep that last column in mind. We come back to it near the end.

Now one small table. The handlers in this post write rows into it, and later on we go and look at what is left in there.

Sample data
CREATE OR REPLACE TABLE TRACE_T149 (
    STEP INT,
    NOTE VARCHAR(80)
);
Result1 row · 1 column
status
Table TRACE_T149 successfully created.

Two columns, a step number and a note. That is all we need.

Let's start with the smallest handler

The smallest handler we can write is one that raises an error on purpose and then catches it.

Have a look at where the handler goes. It is after the statements, at the end of the block, just before END.

Create the procedure
CREATE OR REPLACE PROCEDURE CAUGHT_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'the handler ran';
END;
$$;
Result1 row · 1 column
status
Function CAUGHT_T149 successfully created.

Now let's call it.

Query
CALL CAUGHT_T149();
Result1 row · 1 column
CAUGHT_T149
the handler ran

The handler ran. That is the sentence from the handler, not the one from the body, so the RAISE did fire and the RETURN 'never reached' under it never did.

And the call itself succeeded. We got a result, not an error. That is the first thing to notice about a handler, and the whole post hangs off it.

What happens if we put the handler first?

If you have written try and catch in another language, the handler going last may feel backwards. Let's try putting it first and see what Snowflake says.

Create the procedure
CREATE OR REPLACE PROCEDURE HANDLERFIRST_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
EXCEPTION
    WHEN OTHER THEN
        RETURN 'the handler ran';
    RAISE STATEMENT_ERROR;
END;
$$;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 3 at position 0 unexpected 'EXCEPTION'.

001003, a syntax error, and it points straight at the word EXCEPTION. The procedure was never created.

So the order is fixed. Statements first, handler last. There is no try block to put anything inside.

Now let's catch an error we did not raise ourselves

So far we raised the error ourselves and caught it by name. Real failures do not arrive with a name we chose. Let's divide by zero instead and catch it with WHEN OTHER, which is the catch-all.

Create the procedure
CREATE OR REPLACE PROCEDURE CATCHALL_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    N := 1 / 0;
    RETURN 'never reached';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'caught by OTHER: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function CATCHALL_T149 successfully created.
Query
CALL CATCHALL_T149();
Result1 row · 1 column
CATCHALL_T149
caught by OTHER: Division by zero

Caught, and SQLERRM has the text of the real error in it. We did not have to know what was going to go wrong.

And now the spelling you would use in Oracle

In Oracle and in PL/pgSQL the catch-all is WHEN OTHERS, with an S. Let's write it that way and see.

Create the procedure
CREATE OR REPLACE PROCEDURE CATCHALLS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    N := 1 / 0;
    RETURN 'never reached';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'caught by OTHERS: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function CATCHALLS_T149 successfully created.

That was accepted. Snowflake did not complain at all. Now let's call it.

Query
CALL CATCHALLS_T149();
Query failed — Snowflake returned an error
000904 (42000): SQL compilation error: error line 8 at position 9
invalid identifier 'OTHERS'

000904, invalid identifier 'OTHERS'. The plural does not exist here. Snowflake read it as the name of a condition, looked for one called OTHERS, and did not find it.

Created happily, refused at CALL

Read that pair again, because this is the shape of most of the mistakes in this post. The procedure was created without a word of complaint, and the refusal only arrived when we called it. A handler branch name is not resolved at create time.

Can we catch it by the name in the error message?

The division by zero printed its own text. Snowflake calls that kind of failure an EXPRESSION_ERROR. So can we name it in the branch instead of using the catch-all?

Create the procedure
CREATE OR REPLACE PROCEDURE CATCHEXPR_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    N := 1 / 0;
    RETURN 'never reached';
EXCEPTION
    WHEN EXPRESSION_ERROR THEN
        RETURN 'caught as EXPRESSION_ERROR: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function CATCHEXPR_T149 successfully created.
Query
CALL CATCHEXPR_T149();
Result1 row · 1 column
CATCHEXPR_T149
caught as EXPRESSION_ERROR: Division by zero

We can. So the type name that turns up in Snowflake's own error text is the word you write in the branch. That is worth knowing, because it means the error message is also telling you how to catch it.

Now let's ask the handler what it caught

A handler can read three things about the failure: SQLCODE, SQLSTATE and SQLERRM. Let's build one string out of all three and return it.

Create the procedure
CREATE OR REPLACE PROCEDURE WHATWAS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'code=' || SQLCODE || ' state=' || SQLSTATE || ' msg=' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function WHATWAS_T149 successfully created.
Query
CALL WHATWAS_T149();
Result1 row · 1 column
WHATWAS_T149
NULL

NULL. The whole string came back null.

Concatenating a null in SQL gives you a null, so at least one of those three is null here. This grid cannot tell us which one it was, and we are not going to guess at it.

What it does prove is worth having. A handler that builds its message by sticking values together can hand you a null instead of a message, and you will not get an error telling you so.

And we already know SQLERRM works for a real error, because the two division-by-zero handlers above both printed its text. So the gap is specific to a bare RAISE that carries no message of its own.

Now let's give the handler three branches

A handler can have more than one branch. Let's write one with STATEMENT_ERROR, EXPRESSION_ERROR and a catch-all, and a parameter that decides what goes wrong.

Create the procedure
CREATE OR REPLACE PROCEDURE THREEWAY_T149(WHICH VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    IF (:WHICH = 'raise') THEN
        RAISE STATEMENT_ERROR;
    ELSEIF (:WHICH = 'divide') THEN
        N := 1 / 0;
    ELSE
        RETURN 'nothing went wrong';
    END IF;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'the statement branch ran';
    WHEN EXPRESSION_ERROR THEN
        RETURN 'the expression branch ran';
    WHEN OTHER THEN
        RETURN 'the catch-all ran: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function THREEWAY_T149 successfully created.

Let's raise a STATEMENT_ERROR first.

Query
CALL THREEWAY_T149('raise');
Result1 row · 1 column
THREEWAY_T149
the statement branch ran

Now let's divide by zero instead.

Query
CALL THREEWAY_T149('divide');
Result1 row · 1 column
THREEWAY_T149
the expression branch ran

Each one found its own branch, and the catch-all sat there unused both times.

And when nothing goes wrong at all?

Query
CALL THREEWAY_T149('fine');
Result1 row · 1 column
THREEWAY_T149
nothing went wrong

That is the body's own RETURN. No branch fired. A handler costs nothing when the block works.

Let's declare an exception of our own

We do not have to use Snowflake's names. We can declare an exception ourselves, with a code and a message, and raise that.

Create the procedure
CREATE OR REPLACE PROCEDURE MYOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    TOO_SMALL EXCEPTION (-20001, 'that amount is too small');
BEGIN
    RAISE TOO_SMALL;
    RETURN 'never reached';
EXCEPTION
    WHEN TOO_SMALL THEN
        RETURN 'caught my own: code=' || SQLCODE || ' msg=' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function MYOWN_T149 successfully created.
Query
CALL MYOWN_T149();
Result1 row · 1 column
MYOWN_T149
caught my own: code=-20001 msg=that amount is too small

Both the code and the message carried through to the handler. SQLCODE gave us back the number we chose and SQLERRM gave us back our own sentence.

How short can that declaration be?

Let's drop the code and the message and declare just a name.

Create the procedure
CREATE OR REPLACE PROCEDURE MYOWNBARE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    PLAIN_ONE EXCEPTION;
BEGIN
    RAISE PLAIN_ONE;
    RETURN 'never reached';
EXCEPTION
    WHEN PLAIN_ONE THEN
        RETURN 'caught the bare one: code=' || SQLCODE;
END;
$$;
Result1 row · 1 column
status
Function MYOWNBARE_T149 successfully created.
Query
CALL MYOWNBARE_T149();
Result1 row · 1 column
MYOWNBARE_T149
caught the bare one: code=-20000

That works too, and SQLCODE is -20000. That is the default you get when you do not pick one.

What does the caller see if nobody catches it?

Let's declare one, raise it, and leave the handler out altogether.

Create the procedure
CREATE OR REPLACE PROCEDURE MYOWNLOOSE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    TOO_SMALL EXCEPTION (-20002, 'nobody is going to catch this');
BEGIN
    RAISE TOO_SMALL;
    RETURN 'never reached';
END;
$$;
Result1 row · 1 column
status
Function MYOWNLOOSE_T149 successfully created.
Query
CALL MYOWNLOOSE_T149();
Query failed — Snowflake returned an error
-20002 (P0001): Uncaught exception of type 'TOO_SMALL' on line 5 at position 4 : nobody is going to catch this

The type name we chose and the message we wrote both reached the caller, along with the code. That is worth remembering when you are naming one. Pick a name somebody reading the error will understand.

Can a handler raise something itself?

Yes, and there are two ways to do it. The first is a bare RAISE with no argument, inside the handler.

Create the procedure
CREATE OR REPLACE PROCEDURE RERAISE_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RAISE;
END;
$$;
Result1 row · 1 column
status
Function RERAISE_T149 successfully created.
Query
CALL RERAISE_T149();
Query failed — Snowflake returned an error
-20000 (P0001): Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 4

It re-raised the one it had just caught. The caller gets the STATEMENT_ERROR after all. So a bare RAISE inside a handler means “the one I am handling”.

And the other way: raise a different one

Now let's catch the STATEMENT_ERROR and raise our own exception in its place.

Create the procedure
CREATE OR REPLACE PROCEDURE RAISEOTHER_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    WRAPPED EXCEPTION (-20003, 'wrapped by the handler');
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RAISE WRAPPED;
END;
$$;
Result1 row · 1 column
status
Function RAISEOTHER_T149 successfully created.
Query
CALL RAISEOTHER_T149();
Query failed — Snowflake returned an error
-20003 (P0001): Uncaught exception of type 'WRAPPED' on line 9 at position 8 : wrapped by the handler

Only the new one. The new exception replaced the old one completely, and the caller never hears what actually went wrong.

That is sometimes exactly what you want, because you do not always want internal detail leaving a procedure. Just know that you are throwing the original away when you do it.

Now let's put a block inside a block

Blocks nest. So let's write an inner block with no handler, inside an outer block that has one, and see which one catches the failure.

Create the procedure
CREATE OR REPLACE PROCEDURE NESTED_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    BEGIN
        RAISE STATEMENT_ERROR;
    END;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'the outer handler caught it';
END;
$$;
Result1 row · 1 column
status
Function NESTED_T149 successfully created.
Query
CALL NESTED_T149();
Result1 row · 1 column
NESTED_T149
the outer handler caught it

The outer handler caught it. A failure climbs out of the block it happened in and keeps going until it finds a handler.

Now let's give the inner block its own handler

Create the procedure
CREATE OR REPLACE PROCEDURE NESTEDOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    OUT_TEXT VARCHAR DEFAULT '';
BEGIN
    BEGIN
        RAISE STATEMENT_ERROR;
    EXCEPTION
        WHEN STATEMENT_ERROR THEN
            OUT_TEXT := 'the inner handler ran';
    END;
    RETURN :OUT_TEXT || ', and the outer block carried on';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'the outer handler ran, which it should not have';
END;
$$;
Result1 row · 1 column
status
Function NESTEDOWN_T149 successfully created.
Query
CALL NESTEDOWN_T149();
Result1 row · 1 column
NESTEDOWN_T149
the inner handler ran, and the outer block carried on

The inner handler ran and the outer block carried straight on. The outer handler never fired, and the statements after the inner block still ran.

So the nearest handler wins, and once it has dealt with the failure the block above it does not know anything happened.

What happens when the handler itself fails?

This is the awkward one. Let's write a handler that divides by zero while it is handling a STATEMENT_ERROR.

Create the procedure
CREATE OR REPLACE PROCEDURE BADHANDLER_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        N := 1 / 0;
        RETURN 'never reached either';
END;
$$;
Result1 row · 1 column
status
Function BADHANDLER_T149 successfully created.
Query
CALL BADHANDLER_T149();
Query failed — Snowflake returned an error
100051 (22012): Uncaught exception of type 'EXPRESSION_ERROR' on line 9 at position 13 : Division by zero

Read which error that is. 100051, an EXPRESSION_ERROR, division by zero. That is the new one, from inside the handler.

The STATEMENT_ERROR it was handling is gone. The caller never hears about it, and there is nothing in this message to suggest it ever existed.

So keep the work in a handler small and keep it safe. A handler is the last thing standing between your block and the caller.

Two names that are only checked when you call it

We saw this once already with WHEN OTHERS. Here are the other two cases, because they catch people out in the same way.

First, a RAISE naming an exception that was never declared.

Create the procedure
CREATE OR REPLACE PROCEDURE RAISEUNKNOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RAISE NO_SUCH_EXCEPTION_T149;
    RETURN 'never reached';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'caught it somehow';
END;
$$;
Result1 row · 1 column
status
Function RAISEUNKNOWN_T149 successfully created.

Created without a complaint. Now the call.

Query
CALL RAISEUNKNOWN_T149();
Query failed — Snowflake returned an error
000904 (42000): SQL compilation error: error line 3 at position 10
invalid identifier 'NO_SUCH_EXCEPTION_T149'

000904, and note that the catch-all handler in that procedure did not save it.

Second, a WHEN branch naming a condition that does not exist.

Create the procedure
CREATE OR REPLACE PROCEDURE WHENUNKNOWN_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RAISE STATEMENT_ERROR;
    RETURN 'never reached';
EXCEPTION
    WHEN NO_SUCH_CONDITION_T149 THEN
        RETURN 'never reached either';
END;
$$;
Result1 row · 1 column
status
Function WHENUNKNOWN_T149 successfully created.
Query
CALL WHENUNKNOWN_T149();
Query failed — Snowflake returned an error
000904 (42000): SQL compilation error: error line 6 at position 9
invalid identifier 'NO_SUCH_CONDITION_T149'

000904 again. Neither name is looked up until the procedure runs, so a successful CREATE PROCEDURE tells you nothing about whether the names in your handler are real.

Now the part this post is really about

Let's write a loop that inserts one row per pass and raises at pass three, and let's give it a handler to catch that.

Create the procedure
CREATE OR REPLACE PROCEDURE CAUGHTROWS_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    FOR I IN 1 TO 4 DO
        INSERT INTO TRACE_T149 (STEP, NOTE) VALUES (:I, 'caught run pass ' || :I);
        IF (:I = 3) THEN
            RAISE STATEMENT_ERROR;
        END IF;
    END FOR;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        RETURN 'caught it at pass three';
END;
$$;
Result1 row · 1 column
status
Function CAUGHTROWS_T149 successfully created.

Now let's call it.

Query
CALL CAUGHTROWS_T149();
Result1 row · 1 column
CAUGHTROWS_T149
caught it at pass three

The handler ran, so the call succeeded. From the outside this looks like a procedure that worked.

Now the question that matters. What is in the table?

Query
SELECT STEP, NOTE FROM TRACE_T149 WHERE STEP <= 4 ORDER BY STEP;
Result3 rows · 2 columns
STEPNOTE
1caught run pass 1
2caught run pass 2
3caught run pass 3

Three rows. One, two and three, every one of them still there, written by the passes that ran before the RAISE.

The thing to take away

So catching the exception changed what the caller saw, and nothing else at all. Episode 147 showed that an uncaught failure leaves its rows behind. This shows that a caught one leaves them too. A handler is not an undo.

So where does the rollback go?

In the handler. Let's run the same loop inside a transaction and put a ROLLBACK in the handler before the return.

Create the procedure
CREATE OR REPLACE PROCEDURE ROLLEDBACK_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    BEGIN TRANSACTION;
    FOR I IN 1 TO 4 DO
        INSERT INTO TRACE_T149 (STEP, NOTE) VALUES (:I + 10, 'rolled run pass ' || :I);
        IF (:I = 3) THEN
            RAISE STATEMENT_ERROR;
        END IF;
    END FOR;
    COMMIT;
    RETURN 'never reached';
EXCEPTION
    WHEN STATEMENT_ERROR THEN
        ROLLBACK;
        RETURN 'caught it and rolled back';
END;
$$;
Result1 row · 1 column
status
Function ROLLEDBACK_T149 successfully created.
Query
CALL ROLLEDBACK_T149();
Result1 row · 1 column
ROLLEDBACK_T149
caught it and rolled back

Caught, and rolled back. Now let's count its rows.

Query
SELECT COUNT(*) AS ROLLED_ROWS FROM TRACE_T149 WHERE STEP > 10;
Result1 row · 1 column
ROLLED_ROWS
0

Zero. Nothing it wrote is in the table.

And let's check it did not leave a transaction open behind it, because a handler that returns in the middle of one could.

Query
SELECT CURRENT_TRANSACTION() AS THE_OPEN_TRANSACTION;
Result1 row · 1 column
THE_OPEN_TRANSACTION
NULL

NULL, which means there is no transaction open. The ROLLBACK closed it.

The pattern

This is the pattern to write down. BEGIN TRANSACTION at the top of the body, COMMIT on the happy path, ROLLBACK in the handler. Without that last line, catching the failure leaves every row the block had already written.

Can a handler catch an error from a function?

The failures so far all happened in the block itself. Let's write a JavaScript function that throws its own error, and call it from a block with a catch-all.

Create the function
CREATE OR REPLACE FUNCTION STRICTHALF_T149(N FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
    if (N < 0) { throw "the function will not take a negative"; }
    return N / 2;
$$;
Result1 row · 1 column
status
Function STRICTHALF_T149 successfully created.
Create the procedure
CREATE OR REPLACE PROCEDURE CATCHUDF_T149()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N FLOAT;
BEGIN
    N := STRICTHALF_T149(-1);
    RETURN 'never reached';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'caught the function error: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function CATCHUDF_T149 successfully created.
Query
CALL CATCHUDF_T149();
Result1 row · 1 column
CATCHUDF_T149
caught the function error: JavaScript execution error: Uncaught the function will not take a negative in STRICTHALF_T149 at ' if (N < 0) { throw "the function will not take a negative"; }' position 17 stackstrace: STRICTHALF_T149 line: 2

Caught, and look at what SQLERRM has in it. JavaScript's own text, the name of the function, the line that threw and a position.

So the handler sees across the language boundary. A SQL handler catches an error thrown by JavaScript and can read its message.

One last thing: TRY_CAST is the smaller answer

Here is a problem a handler can solve, and then a smaller way to solve it. Let's guard a cast that will not work.

Create the procedure
CREATE OR REPLACE PROCEDURE CASTHANDLER_T149(S VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER;
BEGIN
    N := :S::NUMBER;
    RETURN 'it cast to ' || :N;
EXCEPTION
    WHEN OTHER THEN
        RETURN 'the cast failed: ' || SQLERRM;
END;
$$;
Result1 row · 1 column
status
Function CASTHANDLER_T149 successfully created.
Query
CALL CASTHANDLER_T149('not a number');
Result1 row · 1 column
CASTHANDLER_T149
the cast failed: Numeric value 'not a number' is not recognized

The handler caught it and told us why. And with an input that does cast?

Query
CALL CASTHANDLER_T149('42');
Result1 row · 1 column
CASTHANDLER_T149
it cast to 42

Fine. So that works. But look at what we built to get there: a procedure, a block, a declared variable and a handler.

Now the same two inputs through TRY_CAST, with no procedure, no block and no handler at all.

Query
SELECT TRY_CAST('not a number' AS NUMBER) AS THE_BAD_ONE,
       TRY_CAST('42' AS NUMBER)           AS THE_GOOD_ONE;
Result1 row · 2 columns
THE_BAD_ONETHE_GOOD_ONE
NULL42

NULL for the one that will not cast, and the number for the one that will. Same problem, two answers, and for a cast the smaller one is usually the right one.

That is worth holding on to generally. A handler is the right tool when the failure is a real failure. When Snowflake already has a function that hands you a null instead, use that.

And let's leave the schema the way we found it

The demo script drops everything it made. Here is the check afterwards.

Query
SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMAANY_OPEN_TRANSACTION
DEMO_DBPUBLICNULL
Query
SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T149');
Result1 row · 1 column
PROBE_TABLES_LEFT
0

Same database and schema, no transaction open, and none of the tables this post created are left behind.

Key points

  • The handler goes at the end of the block, after the statements, just before END. Putting it first is 001003 and the procedure is never created.
  • WHEN OTHER is the catch-all. WHEN OTHERS, with an S, does not exist in Snowflake and gives you 000904 when you call it.
  • The type name in Snowflake's own error text is the word you write in the branch, so EXPRESSION_ERROR and STATEMENT_ERROR are both catchable by name.
  • A handler can read SQLCODE, SQLSTATE and SQLERRM. Concatenating all three after a bare RAISE gave back a NULL, so at least one of them is null there.
  • Declare your own exception with a code and a message and both reach the handler. Leave them out and SQLCODE is -20000.
  • An uncaught exception of your own hands the caller its type name, its code and your message.
  • A bare RAISE in a handler re-raises the one it caught. Raising a different one replaces the original completely.
  • A failure climbs out of an inner block to the nearest handler. Give the inner block its own and the outer one never fires.
  • A handler that itself fails hands the caller the new error. The one it was handling is gone.
  • An undeclared RAISE name and an unknown WHEN name are both refused at CALL, not at CREATE.
  • Catching an exception leaves every row the block already wrote. A handler is not an undo.
  • ROLLBACK belongs in the handler. With it the rows are gone and nothing is left open.
  • A handler catches a JavaScript function's error and SQLERRM carries its text.
  • For a cast, TRY_CAST solves the same problem with no block and no handler at all.
Next in this series

In the next post we build the statement itself at run time: dynamic SQL with EXECUTE IMMEDIATE, binding values with USING, and what one quote mark in the wrong place does to a lookup that concatenates its argument.

Every query, every result grid and every error pane on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The three refusals sit in the demo file after its cleanup, and all three were really run. Every count in the text is read off one of the grids on this page. No account identifier, user name or query id appears anywhere on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake Scripting exceptions documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.