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
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;
Snowflake 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;
$$;
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
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
SSC-EWI-0100: Multiple result sets are returned in temporary tables.
Last updated