SSC-FDM-BQ0002

Exception system variables are not supported in Snowflake.

Description

This FDM is added when a statement references exception variables in BigQuery because they are not supported in Snowflake, and the content of these variables is quite different from the exception variables allowed in Snowflake. For more information, please refer to Handling Exceptions in Snowflake.

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

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

Result

[{
  "message": "Query error: division by zero: 1 / 0 at [snowflake-snowconvert-team.test.proc1:2:3]",
  "stack_trace": [{
    "line": "2",
    "column": "3",
    "filename": null,
    "location": "snowflake-snowconvert-team.test.proc1"
  }, {
    "line": "1",
    "column": "1",
    "filename": null,
    "location": null
  }],
  "statement_text": "SELECT 1/0",
  "formatted_stack_trace": "At snowflake-snowconvert-team.test.proc1[2:3]\nAt [1:1]\n"
}]

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE PROCEDURE test.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "04/09/2025",  "domain": "test" }}'
AS
$$
    BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHER THEN
--      --** SSC-FDM-BQ0002 - EXCEPTION SYSTEM VARIABLES ARE NOT SUPPORTED IN SNOWFLAKE. **
--    SELECT
--      @@error.message as message,
--      @@error.stack_trace as stack_trace,
--      @@error.statement_text as statement_text,
--      @@error.formatted_stack_trace as formatted_stack_trace;
      RETURN OBJECT_CONSTRUCT('SQLERRM', SQLERRM, 'SQLCODE', SQLCODE, 'SQLSTATE', SQLSTATE);
    END;
$$;

Result

{
  "SQLCODE": 100051,
  "SQLERRM": "Division by zero",
  "SQLSTATE": "22012"
}

Recommendations

• Snowflake has three built-in variables that provide information about the exception:

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

  2. SQLERRM: This is an error message.

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

• If you need more support, you can email us at [email protected]