Handling exceptions

In a Snowflake Scripting block, you can raise an exception if an error occurs. You can also handle exceptions that occur in your Snowflake Scripting code.

Introduction

Snowflake Scripting raises an exception if an error occurs while executing a statement (e.g. if a statement attempts to DROP a table that doesn’t exist). An exception prevents the next lines of code from executing.

In a Snowflake Scripting block, you can write exception handlers that catch specific types of exceptions declared in that block and in blocks nested inside that block.

In addition, for errors that can occur in your code, you can define your own exceptions that you can raise when errors occur.

When an exception is raised in a Snowflake Scripting block (either by your code or by a statement that fails to execute), Snowflake Scripting attempts to find a handler for that exception:

  • If the block in which the exception occurred has a handler for that exception, then execution resumes at the beginning of that exception handler.

  • If the block does not have its own exception handler, then the exception can be caught by the enclosing block.

    If the exception occurs more than one layer deep, then the exception is sent upward one layer at a time until either:

    • A layer with an appropriate exception handler handles the exception.

    • The outermost layer is reached, in which case an error occurs.

  • If there is no handler for the exception in the current block or in any enclosing blocks, execution of the block stops, and the client that submits the block for execution (e.g. the web interface, SnowSQL, etc.) reports this as a Snowflake error.

An exception handler can contain its own exception handler in case an exception occurs while handling another exception.

Declaring an exception

You can declare your own exception in the DECLARE section of the block. Use the syntax described in Exception declaration syntax. For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
Copy

Raising a declared exception

To raise an exception, execute the RAISE command. For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
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
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
END;
$$
;
Copy

Execution stops at the point when the exception is raised. (In the example, counter is never incremented and returned.)

The client that submits this block for execution (e.g. Snowsight) reports an error and indicates that the exception was not caught:

-20002 (P0001): Uncaught exception of type 'MY_EXCEPTION' on line 8 at position 4 : Raised MY_EXCEPTION.
Copy

If you want to add code to handle any exceptions that you raise (as well as exceptions raised when statements fail to execute), you can write exception handlers. See Handling an exception.

Note

In an exception handler, if you need to raise the same exception again, see Raising the same exception again in an exception handler.

Handling an exception

You can explicitly handle an exception by catching it with an EXCEPTION clause, or you can allow the block to pass the exception on to the enclosing block.

Within the EXCEPTION clause, use a WHEN clause to handle an exception by name. You can handle exceptions that you declare as well as built-in exceptions. Currently, Snowflake provides the following built-in exceptions:

  • STATEMENT_ERROR: This exception indicates an error while executing a statement. For example, if you attempt to drop a table that does not exist, this exception is raised.

  • EXPRESSION_ERROR: This exception indicates an error related to an expression. For example, if you create an expression that evaluates to a VARCHAR, and you attempt to assign the value of the expression to a FLOAT, this error is raised.

When an exception occurs, you can get information about the exception by reading the following three built-in variables:

  • SQLCODE: This is a 5-digit signed integer. For user-defined exceptions, this is the exception_number shown in the syntax for declaring an exception.

  • SQLERRM: This is an error message. For user-defined exceptions, this is the exception_message shown in the syntax for declaring an exception.

  • SQLSTATE: This is a 5-character code modeled on the ANSI SQL standard SQLSTATE. Snowflake uses additional values beyond those in the ANSI SQL standard.

To handle all other exceptions that do not have a WHEN clause, use a WHEN OTHER THEN clause.

For example:

DECLARE
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
EXCEPTION
  WHEN statement_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN my_exception THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  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
  my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
BEGIN
  LET counter := 0;
  LET should_raise_exception := true;
  IF (should_raise_exception) THEN
    RAISE my_exception;
  END IF;
  counter := counter + 1;
  RETURN counter;
EXCEPTION
  WHEN statement_error THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN my_exception THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
END;
$$
;
Copy

This example handles each type of exception by calling OBJECT_CONSTRUCT to construct and return an object that contains the details about the exception. The example produces the following output:

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| {                                    |
|   "Error type": "MY_EXCEPTION",      |
|   "SQLCODE": -20002,                 |
|   "SQLERRM": "Raised MY_EXCEPTION.", |
|   "SQLSTATE": "P0001"                |
| }                                    |
+--------------------------------------+

In rare cases, you might want to explicitly handle an exception by doing nothing. This allows you to continue, rather than abort, when the exception occurs. For more information, see the NULL command.

If you do not set up a handler for an exception, the client that submits the block for execution (e.g. the web interface) reports an error (as explained in Raising a declared exception).

-20002 (P0001): Uncaught exception of type 'MY_EXCEPTION' on line 8 at position 4 : Raised MY_EXCEPTION.
Copy

Note

If you need to raise the same exception again, see Raising the same exception again in an exception handler.

Raising the same exception again in an exception handler

In some cases, you might need to raise the same exception that you caught in your exception handler. In these cases, execute the RAISE command without specifying any arguments.

For example, suppose that during exception handling, you need to capture some details about the exception before raising the same exception again. After capturing the details, execute the RAISE command:

BEGIN
  SELECT * FROM non_existent_table;
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO myexceptions VALUES (:line);
    RAISE; -- Raise the same exception that you are handling.
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 $$
BEGIN
  SELECT * FROM non_existent_table;
EXCEPTION
  WHEN OTHER THEN
    LET LINE := SQLCODE || ': ' || SQLERRM;
    INSERT INTO myexceptions VALUES (:line);
    RAISE; -- Raise the same exception that you are handling.
END;
$$;
Copy

Passing variables to an exception handler

You can pass variables to an exception handler. The exception handler can execute code based on the value of the variable, and the variable value can be returned in error messages.

For a variable to be passed to a handler in the EXCEPTION section, the variable must be declared in the DECLARE section. If a variable is declared in the BEGIN … END section of the block, it can’t be accessed in the EXCEPTION section.

In addition, if you are writing a Snowflake Scripting stored procedure that accepts arguments, you can use those arguments in an exception handler.

For example, the following anonymous block passes the value of the counter_val variable to the exception handler:

DECLARE
  counter_val INTEGER DEFAULT 0;
  my_exception EXCEPTION (-20002, 'My exception text');
BEGIN
  WHILE (counter_val < 12) DO
    counter_val := counter_val + 1;
    IF (counter_val > 10) THEN
      RAISE my_exception;
    END IF;
  END WHILE;
  RETURN counter_val;
EXCEPTION
  WHEN my_exception THEN
    RETURN 'Error ' || sqlcode || ': Counter value ' || counter_val || ' exceeds the limit of 10.';
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
  counter_val INTEGER DEFAULT 0;
  my_exception EXCEPTION (-20002, 'My exception text');
BEGIN
  WHILE (counter_val < 12) DO
    counter_val := counter_val + 1;
    IF (counter_val > 10) THEN
      RAISE my_exception;
    END IF;
  END WHILE;
  RETURN counter_val;
EXCEPTION
  WHEN my_exception THEN
    RETURN 'Error ' || sqlcode || ': Counter value ' || counter_val || ' exceeds the limit of 10.';
END;
$$
;
Copy

The block returns the following error message:

+---------------------------------------------------------+
| anonymous block                                         |
|---------------------------------------------------------|
| Error -20002: Counter value 11 exceeds the limit of 10. |
+---------------------------------------------------------+

The following is an example of a Snowflake Scripting stored procedure that passes in an argument. The example demonstrates how you can use the argument in an exception handler:

CREATE OR REPLACE PROCEDURE exception_test_vars(amount INT)
  RETURNS TEXT
  LANGUAGE SQL
AS
DECLARE
  my_exception_1 EXCEPTION (-20002, 'Value too low');
  my_exception_2 EXCEPTION (-20003, 'Value too high');
BEGIN
  CREATE OR REPLACE TABLE test_order_insert(units INT);
  IF (amount < 1) THEN
    RAISE my_exception_1;
  ELSEIF (amount > 10) THEN
    RAISE my_exception_2;
  ELSE
    INSERT INTO test_order_insert VALUES (:amount);
  END IF;
  RETURN 'Order inserted successfully.';
EXCEPTION
  WHEN my_exception_1 THEN
    RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too low (1 or greater required).';
  WHEN my_exception_2 THEN
    RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too high (exceeds limit of 10).';
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):

CREATE OR REPLACE PROCEDURE exception_test_vars(amount INT)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
DECLARE
  my_exception_1 EXCEPTION (-20002, 'Value too low');
  my_exception_2 EXCEPTION (-20003, 'Value too high');
BEGIN
  CREATE OR REPLACE TABLE test_order_insert(units INT);
  IF (amount < 1) THEN
    RAISE my_exception_1;
  ELSEIF (amount > 10) THEN
    RAISE my_exception_2;
  ELSE
    INSERT INTO test_order_insert VALUES (:amount);
  END IF;
  RETURN 'Order inserted successfully.';
EXCEPTION
  WHEN my_exception_1 THEN
    RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too low (1 or greater required).';
  WHEN my_exception_2 THEN
    RETURN 'Error ' || sqlcode || ': Submitted amount ' || amount || ' is too high (exceeds limit of 10).';
END;
$$
;
Copy

The following calls to the stored procedure show the expected output:

CALL exception_test_vars(7);
Copy
+------------------------------+
| EXCEPTION_TEST_VARS          |
|------------------------------|
| Order inserted successfully. |
+------------------------------+

CALL exception_test_vars(-3);
Copy
+-----------------------------------------------------------------------+
| EXCEPTION_TEST_VARS                                                   |
|-----------------------------------------------------------------------|
| Error -20002: Submitted amount -3 is too low (1 or greater required). |
+-----------------------------------------------------------------------+

CALL exception_test_vars(20);
Copy
+----------------------------------------------------------------------+
| EXCEPTION_TEST_VARS                                                  |
|----------------------------------------------------------------------|
| Error -20003: Submitted amount 20 is too high (exceeds limit of 10). |
+----------------------------------------------------------------------+
Language: English