SQL TRY CATCH and Error Handling with Examples

Last modified date:

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

BEGIN TRY
BEGIN TRANSACTION
      --SQL kód zde
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

  • 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.

TRY CATCH Example with Error Handling Logging into SQL Table

Lets for example induce 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

SQL TRY 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.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Commands Tags: , ,

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.