TRY CATCH

Translation reference for TRY CATCH statement in Transact-SQL.

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.

Applies to

• SQL Server

• Azure Synapse Analytics

Description

Implements error handling for Transact SQL. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is usually passed to another group of statements that is enclosed in a CATCH block.

Sample Source Patterns

The following example details the transformation for TRY CATCH inside procedures.

Transact-SQL

Input

IN -> SqlServer_01.sql

CREATE PROCEDURE ERROR_HANDLING_PROC
AS
BEGIN
    BEGIN TRY  
        -- Generate divide-by-zero error.  
        SELECT 1/0;  
    END TRY  
    BEGIN CATCH  
        -- Execute error retrieval routine.  
        SELECT 'error';
    END CATCH;   
END;

Output

Snowflake SQL

Input

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE ERROR_HANDLING_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        BEGIN
            -- Generate divide-by-zero error.  
            SELECT
                TRUNC( 1/0);
        EXCEPTION
            WHEN OTHER THEN
                -- Execute error retrieval routine.  
                SELECT 'error';
        END;
    END;
$$;

Output

Try catch outside routines (functions and procedures)

Transact-SQL

IN -> SqlServer_02.sql

BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT 'error';
END CATCH;

Snowflake Scripting

OUT -> SqlServer_02.sql

DECLARE
    BlockResultSet1 VARCHAR;
    BlockResultSet2 VARCHAR;
    return_arr ARRAY := array_construct();
BEGIN
    BEGIN
        BlockResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet1) AS
            SELECT
                TRUNC( 1/0);
        return_arr := array_append(return_arr, :BlockResultSet1);
    EXCEPTION
        WHEN OTHER THEN
            BlockResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
            CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet2) AS
                SELECT 'error';
            return_arr := array_append(return_arr, :BlockResultSet2);
    END;
    --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
END;

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-0020: Multiple result sets are returned in temporary tables.