GET DIAGNOSTICS EXCEPTION

Translation reference to convert Teradata GET DIAGNOSTICS EXCEPTION statement to Snowflake Scripting

Description

GET DIAGNOSTICS retrieves information about successful, exception, or completion conditions from the Diagnostics Area.

For more information regarding Teradata GET DIAGNOSTICS, check here.

GET DIAGNOSTICS
{
  [ EXCEPTION < condition_number >
    [ < parameter_name | variable_name > = < information_item > ]...
  ] 
  |
  [ < parameter_name | variable_name > = < information_item > ]...
}

Sample Source Patterns

Teradata

Query

CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 1
        V_MESSAGE = Message_Text,
        V_CODE = RETURNED_SQLSTATE;
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET V_MESSAGE VARCHAR(200);
      LET V_CODE VARCHAR(200);
      LET V_Result INTEGER;
      
      SELECT c1 INTO V_Result FROM PUBLIC.tab1;
      V_MESSAGE := SQLERRM;
      V_CODE := SQLSTATE;
   END;
$$;

Known Issues

CLASS_ORIGIN, CONDITION_NUMBER

The use of GET DIAGNOSTICS for CLASS_ORIGIN, CONDITION_NUMBER is not supported

Teradata

Query

CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 5
        V_CLASS = CLASS_ORIGIN,
        V_COND = CONDITION_NUMBER;
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET V_MESSAGE VARCHAR(200);
      LET V_CODE VARCHAR(200);
      LET V_Result INTEGER;
      
      SELECT c1 INTO V_Result FROM PUBLIC.tab1;
      /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CLASS_ORIGIN' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*        V_CLASS = CLASS_ORIGIN*/
       
      /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CONDITION_NUMBER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*        V_COND = CONDITION_NUMBER*/
       
   END;
$$;

Related EWIS

1. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting