MSCINF0007

Exception system variables are not supported in Snowflake.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

None

Description

This EWI 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

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

CREATE OR REPLACE PROCEDURE test.proc1()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHER THEN
--    --** MSC-INFORMATION - MSCINF0007 - 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 [e-mail]