SQL TRY CATCH command is designed to control code flow in case that error situation occurs (error handling). This construction consists of  blocks. It is also possible to use transaction (more in article on Transactions). If an error occurs in the first block – TRY..END like that is activated code in CATCH..END block.

TRY CATCH Syntax in SQL Server

The code below consists of

• TRY part: Script containing SQL DML or DDL (Create table, INSERT, UPDATE, etc.) is typically located in the first block. Eventually there is also a beginning of transaction BEGIN TRANSACTION and COMMIT command in the end (in case an error does not occur).
• CATCH part: Call out of function/s describing error is located here (error messages, severities etc.) with occasional error log and also the ROLLBACK command.

BEGIN TRY
BEGIN TRANSACTION
      --SQL code here
    COMMIT
END TRY
BEGIN CATCH
      --Do something if error occurs
    ROLLBACK
END CATCH

TRY CATCH Example with Error Handling Logging into SQL Table

Lets for example raise an Error message by dividing by zero. We will perform a ROLLBACK in CATCH part in case of error (which will occur). Following that, we will save properties of the error into temp table for further analysis:

SQL Script:

BEGIN TRY
BEGIN TRANSACTION

  SELECT 1/0 AS [Result]
  INTO #Temp;

COMMIT;

END TRY
BEGIN CATCH

     ROLLBACK;

  SELECT
    ERROR_NUMBER()    AS [ErrorNumber],
    ERROR_SEVERITY()  AS [ErrorSeverity],
    ERROR_STATE()     AS [ErrorState],
    ERROR_PROCEDURE() AS [ErrorProcedure],
    ERROR_LINE()      AS [ErrorLine],
    ERROR_MESSAGE()   AS [ErrorMessage]
  INTO #Error_Log;

END CATCH

Script in itself performed correctly. The error (Divide by zero error encoutered) have been caught and Error parameters are located in temp table #Error_Log

SELECT * FROM #Error_Log;

Let’s check that there is no transaction still opened and that transaction has been rolled back.

SELECT @@TRANCOUNT;

Note: If we would place ROLLBACK after Error log, then the logging itself would be in transaction. Following rollback would make us lose it. SQL TRY CACTH would however perform correctly and transaction would be withdrawn (sadly even with logging). That means that order of commands in CATCH matters.