BEGIN...EXCEPTION...END

BEGIN...EXCEPTION executes a block of statements. If any of the statements encounter an error, the remainder of the block is skipped and the statements in the EXCEPTION clause are executed.

Grammar Syntax

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Click here to go to the BigQuery specification for this syntax.

When an exception occurs, a return is added in order to provide information about the exception by reading the following three built-in variables:

• SQLCODE: This is a 5-digit signed integer.

• SQLERRM: This is an error message.

• SQLSTATE: This is a 5-character code modeled on the ANSI SQL standard SQLSTATE .

Sample Source Patterns

Basic case

BigQuery

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

Snowflake

BEGIN
  BEGIN
  ...
  EXCEPTION WHEN OTHER THEN
    SELECT 1/0;
    RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
  END;
EXCEPTION WHEN OTHER THEN
  -- The exception thrown from the inner exception handler lands here.
  RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
END;

Exception System Variables

These variables are not supported in Snowflake, the content of these variables is different from the exception variables allowed in Snowflake. For more information please refer to Handling Exceptions in Snowflake.

BigQuery

CREATE OR REPLACE PROCEDURE test.proc1()
BEGIN
  BEGIN
  ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  INSERT INTO test.exceptions VALUES (
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace);
END;

Snowflake

CREATE OR REPLACE PROCEDURE test.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
  BEGIN
  ...
  EXCEPTION WHEN OTHER THEN
    SELECT 1/0;
    RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
  END;
EXCEPTION WHEN OTHER THEN
--    --** MSC-INFORMATION - MSCINF0007 - EXCEPTION SYSTEM VARIABLES ARE NOT SUPPORTED IN SNOWFLAKE. **
--  INSERT INTO test.exceptions VALUES (
--    @@error.message,
--    @@error.stack_trace,
--    @@error.statement_text,
--    @@error.formatted_stack_trace);
    RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
  END;
$$;

Related EWIs

1. MSCINF0007: Exception system variables are not supported in Snowflake.