SSC-FDM-BQ0002

Exception system variables are not supported in Snowflake.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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:

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;

Output Code:

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;
$$;

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]

Last updated