TRY CATCH

Translation reference for TRY CATCH statement in SQL Server.

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.

SQL Server

Input

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

CREATE OR REPLACE PROCEDURE ERROR_HANDLING_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
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)

SQL Server

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

Snowflake Scripting

DECLARE
    return_arr ARRAY := array_construct();
BEGIN
    BEGIN
        SELECT
            TRUNC( 1/0);
    EXCEPTION
        WHEN OTHER THEN
        SELECT 'error';
    END;
    --** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
END;

Known Issues

No issues were found.

Related EWIs

No related EWIs.