EXCEPTION (Snowflake Scripting)¶

Specifies how to handle exceptions raised in the Snowflake Scripting block.

For more information on exceptions, see Handling exceptions.

See also:: RAISE

Syntax¶

EXCEPTION
    WHEN <exception_name> [ OR <exception_name> ... ] THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ WHEN OTHER THEN ]
        <statement>;
        [ <statement>; ... ]

Copy

Where:

exception_name
An exception name defined in the DECLARE portion of the current block, or in an enclosing block.

statement
A statement can be any of the following:

A single SQL statement (including CALL).

A control-flow statement (for example, a looping or branching statement).

A nested block.

Usage notes¶

Each block can have its own exception handler.
Snowflake supports no more than one exception handler per block. However, that handler can catch more than one type of exception by having more than one WHEN clause.
The exception handler should be at the end of the block. If the block contains statements after the exception handler, those statements are not executed.
The WHEN OTHER THEN clause catches any exception not yet specified.
If more than one WHEN clause could match a specific exception, then the first WHEN clause that matches is the one that is executed. The other clauses are not executed.
An exception handler can handle a specified exception only if that specified exception is in scope.
If a stored procedure is intended to return a value, then it should return a value from each possible path, including each WHEN clause of the exception handler.
To use a variable in an exception handler, the variable must be declared in the DECLARE section or passed as an argument to a stored procedure. It can’t be declared in the BEGIN … END section. For more information, see Passing variables to an exception handler.

Examples¶

This example declares, raises, and handles an exception. Note that:

The exception handler is designed to handle more than one type of exception.

One of the WHEN clauses in the exception handler contains a single statement, while the other contains a block.

DECLARE
  RESULT VARCHAR;
  EXCEPTION_1 EXCEPTION (-20001, 'I caught the expected exception.');
  EXCEPTION_2 EXCEPTION (-20002, 'Not the expected exception!');
BEGIN
  RESULT := 'If you see this, I did not catch any exception.';
  IF (TRUE) THEN
    RAISE EXCEPTION_1;
  END IF;
  RETURN RESULT;
EXCEPTION
  WHEN EXCEPTION_2 THEN
    RETURN SQLERRM;
  WHEN EXCEPTION_1 THEN
    RETURN SQLERRM;
END;

Copy

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
    DECLARE
        RESULT VARCHAR;
        EXCEPTION_1 EXCEPTION (-20001, 'I caught the expected exception.');
        EXCEPTION_2 EXCEPTION (-20002, 'Not the expected exception!');
    BEGIN
        RESULT := 'If you see this, I did not catch any exception.';
        IF (TRUE) THEN
            RAISE EXCEPTION_1;
        END IF;
        RETURN RESULT;
    EXCEPTION
        WHEN EXCEPTION_2 THEN
            RETURN SQLERRM;
        WHEN EXCEPTION_1 THEN
            RETURN SQLERRM;
    END;
$$
;

Copy

Here is the output of executing the example that raises the exception. This shows that the exception handler caught the exception.

+----------------------------------+
| anonymous block                  |
|----------------------------------|
| I caught the expected exception. |
+----------------------------------+

Copy

This next example is similar to the previous example, but demonstrates nested blocks, and shows that an inner block can raise an exception declared in either the inner block or in an outer block.

DECLARE
  e1 EXCEPTION (-20001, 'Exception e1');

BEGIN
  -- Inner block.
  DECLARE
    e2 EXCEPTION (-20002, 'Exception e2');
    selector BOOLEAN DEFAULT TRUE;
  BEGIN
    IF (selector) THEN
      RAISE e1;
    ELSE
      RAISE e2;
    END IF;
  END;

EXCEPTION
  WHEN e1 THEN
    RETURN SQLERRM || ' caught in outer block.';
END;

Copy

EXECUTE IMMEDIATE $$
    DECLARE
        e1 EXCEPTION (-20001, 'Exception e1');

    BEGIN
        -- Inner block.
        DECLARE
            e2 EXCEPTION (-20002, 'Exception e2');
            selector BOOLEAN DEFAULT TRUE;
        BEGIN
            IF (selector) THEN
                RAISE e1;
            ELSE
                RAISE e2;
            END IF;
        END;

    EXCEPTION
        WHEN e1 THEN
            BEGIN
                RETURN SQLERRM || ' caught in outer block.';
            END;

    END;
$$
;

Copy

Here is the output of executing the example that raises the exception. This shows that the exception handler caught the exception.

+-------------------------------------+
| anonymous block                     |
|-------------------------------------|
| Exception e1 caught in outer block. |
+-------------------------------------+

Copy

This next example is similar to the previous example, but demonstrates nested blocks, each of which has its own exception handler.

DECLARE
  RESULT VARCHAR;
  e1 EXCEPTION (-20001, 'Outer exception e1');

BEGIN
  RESULT := 'No error so far (but there will be).';

  DECLARE
    e1 EXCEPTION (-20101, 'Inner exception e1');
  BEGIN
    RAISE e1;
  EXCEPTION
    WHEN e1 THEN
      RESULT := 'Inner exception raised.';
      RETURN RESULT;
  END;

  RETURN RESULT;

EXCEPTION
  WHEN e1 THEN
    RESULT := 'Outer exception raised.';
    RETURN RESULT;

END;

Copy

EXECUTE IMMEDIATE $$
    DECLARE
        RESULT VARCHAR;
        e1 EXCEPTION (-20001, 'Outer exception e1');

    BEGIN
        RESULT := 'No error so far (but there will be).';

        DECLARE
            e1 EXCEPTION (-20101, 'Inner exception e1');
        BEGIN
            RAISE e1;
        EXCEPTION
            WHEN e1 THEN
                RESULT := 'Inner exception raised.';
                RETURN RESULT;
        END;

        RETURN RESULT;

    EXCEPTION
        WHEN e1 THEN
            RESULT := 'Outer exception raised.';
            RETURN RESULT;

    END;
$$
;

Copy

Note

This example uses the same exception name (e1) in the outer and inner blocks.

This is not recommended.

The example does this to illustrate the scope of exception names. The two exceptions with the name e1 are different exceptions.

The e1 handler in the outer block does not handle the exception e1 that is declared and raised in the inner block.

Here is the output of executing the example that raises the exception. This shows that the inner exception handler ran.

+-------------------------+
| anonymous block         |
|-------------------------|
| Inner exception raised. |
+-------------------------+

Copy

This example fragment shows:

How to catch more than one exception in the same clause by using OR.

How to catch unspecified exceptions by using WHEN OTHER THEN.

EXCEPTION
  WHEN MY_FIRST_EXCEPTION OR MY_SECOND_EXCEPTION OR MY_THIRD_EXCEPTION THEN
    RETURN 123;
  WHEN MY_FOURTH_EXCEPTION THEN
    RETURN 4;
  WHEN OTHER THEN
    RETURN 99;

Copy

The following example shows how to return SQLCODE, SQLERRM (SQL error message), and SQLSTATE when catching an exception:

DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'Sample message');
BEGIN
  RAISE MY_EXCEPTION;
EXCEPTION
  WHEN STATEMENT_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN EXPRESSION_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
END;

Copy

EXECUTE IMMEDIATE $$
    DECLARE
        MY_EXCEPTION EXCEPTION (-20001, 'Sample message');
    BEGIN
        RAISE MY_EXCEPTION;
    EXCEPTION
        WHEN STATEMENT_ERROR THEN
            RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                                    'SQLCODE', SQLCODE,
                                    'SQLERRM', SQLERRM,
                                    'SQLSTATE', SQLSTATE);
        WHEN EXPRESSION_ERROR THEN
            RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR',
                                    'SQLCODE', SQLCODE,
                                    'SQLERRM', SQLERRM,
                                    'SQLSTATE', SQLSTATE);
        WHEN OTHER THEN
            RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                                    'SQLCODE', SQLCODE,
                                    'SQLERRM', SQLERRM,
                                    'SQLSTATE', SQLSTATE);
    END;
$$
;

Copy

Running this example produces the following output:

+--------------------------------+
| anonymous block                |
|--------------------------------|
| {                              |
|   "Error type": "Other error", |
|   "SQLCODE": -20001,           |
|   "SQLERRM": "Sample message", |
|   "SQLSTATE": "P0001"          |
| }                              |
+--------------------------------+

Copy

This example demonstrates returning a value from each possible path:

declare
    e1 exception;
    e2 exception;
begin
    statement_1;
    ...
    RETURN x;
exception
    when e1 then begin
        ...
        RETURN y;
        end;
    when e2 then begin
        ...
        RETURN z;
        end;
end;

Copy