Error Handling in SQL Server with Try Catch

By:   |   Comments (7)   |   Related: > TSQL


Problem

An exception is defined as a runtime error that can be handled programmatically. If not handled, it might terminate the flow of a program. Modern day programming languages like C#, Java, etc., have built-in support for exception handling. SQL Server is adept at error handling as well - it has built-in support for constructs like TRY, CATCH that can be used to handle errors elegantly. Incidentally the THROW statement was introduced in SQL Server 2012 and its inclusion makes error handling in SQL Server much easier. This article talks about how you can handle errors in SQL Server using TRY...CATCH statements.

Solution

In this section, we’ll discuss how you can handle errors in SQL Server.

Error Handling in SQL Server

Here's how error handling in SQL Server works. In SQL Server you can take advantage of TRY...CATCH statements to handle errors. When writing code that handles errors, you should have a TRY block and a CATCH block immediately after it. The TRY block starts with a BEGIN TRY statement and ends with an END TRY statement. Similarly, the CATCH block starts with a BEGIN CATCH statement and ends with an END CATCH statement.

Assume that there are a group of statements in the TRY block, i.e., enclosed between the BEGIN TRY...END TRY statements. As soon as an error occurs in this TRY block, the control moves to the CATCH block where you might have another group of statements for handling errors. In this regard, the following points should be noted:

A TRY block should be immediately be followed by a CATCH block where the error handling code resides. Here's an example code that illustrates this.

BEGIN TRY  
   -- Write statements here that may cause exception
END TRY
BEGIN CATCH  
   -- Write statements here to handle exception
END CATCH

When an error occurs inside the TRY block, the control moves to the first statement inside the CATCH block. On the contrary, if the statements inside a TRY block have completed execution successfully without an error, the control will not flow inside the CATCH block. Rather, the first statement immediately after the END CATCH statement will then be executed. In this article we’ll take advantage of the Northwind database to run our queries. If you don’t have a copy of it in your computer, you can download one from here.

Retrieving detailed information on the error

You can take advantage of various functions inside the CATCH block to get detailed information about an error.

These functions include the following:

  • ERROR_MESSAGE() - you can take advantage of this function to get the complete error message.
  • ERROR_LINE() - this function can be used to get the line number on which the error occurred.
  • ERROR_NUMBER() - this function can be used to get the error number of the error.
  • ERROR_SEVERITY() - this function can be used to get the severity level of the error.
  • ERROR_STATE() - this function can be used to get the state number of the error.
  • ERROR_PROCEDURE() - this function can be used to know the name of the stored procedure or trigger that has caused the error.

Programming TRY…CATCH Blocks in SQL Server

Note that you cannot use TRY...CATCH blocks inside T-SQL UDFs. If you have to capture errors that occur inside a UDF, you can do that in the calling procedure or code.

Now consider the following code snippet that illustrates how an error generated inside a TRY block is handled in the CATCH block and the relevant error metadata displayed.

BEGIN TRY
   Insert Into Categories(CategoryID, CategoryName, Description, Picture) Values (9, 'Test', 'Test Description', 'Test')
END TRY
BEGIN CATCH
   SELECT ERROR_MESSAGE() AS [Error Message]
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_NUMBER() AS [Error Number]  
         ,ERROR_SEVERITY() AS [Error Severity]  
         ,ERROR_STATE() AS [Error State]  
END CATCH

Once you run the code block shown above, here’s how the output looks in SQL Server Management Studio (SSMS).

query results

Figure 1

As evident from the error shown in Figure 1, you are not allowed to insert a value in an identity column whose IDENTITY_INSERT is set to OFF.

Let's try to delete a record now.

We’ll attempt to delete a record in the Employees table. Since EmployeeID is a ForeignKey in the Orders table and the EmployeeID being deleted has already been used there, the Delete statement should fail. The following code snippet illustrates this.

BEGIN TRY
   Delete  FROM [northwind].[dbo].[Employees] Where [EmployeeID] = 9
END TRY
BEGIN CATCH
   SELECT ERROR_MESSAGE() AS [Error Message]
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_NUMBER() AS [Error Number]  
         ,ERROR_SEVERITY() AS [Error Severity]  
         ,ERROR_STATE() AS [Error State]  
END CATCH

When you execute the above code, the error metadata is captured as shown in Figure 2.

query results

Figure 2

Error Propagation

When an error occurs inside a TRY block in SQL Server, the statements that immediately follow aren’t executed as the control enters the CATCH block and the first statement inside the CATCH block is executed. Consider the following code snippet:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'This statement will not be executed'
END TRY
BEGIN CATCH 
   PRINT 'The error message is: ' + error_message()
END CATCH

When you execute the preceding code snippet, you'll observe that the statement immediately after the SELECT statement is not executed. The reason is because an exception occurs in the previous statement, i.e., in the line having the SELECT statement. As soon as the exception occurs, the control moves inside the CATCH block and the PRINT statement inside it is executed. Figure 3 shows how the output looks when the code snippet is executed in SQL Server Management Studio.

query results

Figure 3

Using Nested TRY...CATCH Constructs

TRY...CATCH blocks can be nested as well. Nested TRY...CATCH blocks enable you to handle specific errors. As an example, assume that you have to insert multiple records in a database table and you want to know the failed insert statements. A nested TRY...CATCH block is one which resides inside another TRY...CATCH block, i.e., you have an outer TRY…CATCH block and an inner TRY…CATCH block.

The following code snippet illustrates how this can be accomplished.

BEGIN TRY
    --- Write statements here that might cause exceptions
END TRY
BEGIN CATCH
    -- Write statements here to handle the exception
    BEGIN TRY
        --- This is a nested TRY block. Write statements here that might cause exceptions
    END TRY
    BEGIN CATCH
        -- This is a nested CATCH block. Write statements here to handle the exception
    END CATCH
END CATCH

The THROW Statement

The THROW statement in SQL Server raises an exception and transfers the control to a CATCH block. The following code snippet shows the syntax of the THROW statement.

THROW [error_number,  message,  state];

If you don't specify any parameters, then the THROW statement should be placed inside a CATCH block to raise the error that has been handled by the CATCH block. The following code snippet illustrates how you can work with the THROW statement.

BEGIN TRY
   Delete FROM [northwind].[dbo].[Employees] Where [EmployeeID] = 9
END TRY
BEGIN CATCH
   THROW 50000, N'Unable to delete record...', 1;
END CATCH

THROW vs RAISERROR

Note that both THROW and RAISERROR statements can be used to generate custom errors and re-throw exceptions. However, there are subtle differences between the two. While the THROW statement can re-throw the original exception that has been caught in the CATCH block, the RAISE ERROR statement re-throws an altogether new exception and the original exception is lost.

Points to Consider

Before we conclude, here’s a quick recap of what we learned thus far:

  • A TRY...CATCH block will not be able to catch an error if the severity of the error is less than or equal to 10.
  • You cannot have TRY...CATCH inside user defined functions
  • If you have a syntax error inside the TRY block, the CATCH block will not be encountered.

Summary

The built-in support for exception handling in SQL Server enables you to implement error handling code with ease. You can take advantage of this built-in exception handling mechanism in SQL Server to rollback transactions when an error has occurred or use it in DML statements (INSERT, UPDATE and DELETE) to check for errors and handle it gracefully. You can even log errors that are caught inside the CATCH block – I’ll discuss on it and a few advanced features in a future article here.

Next Steps
  • Take advantage of exception handling using TRY...CATCH blocks for simple, powerful, error management in SQL Server
  • If you don’t have a copy of SQL Server installed in your system, you can get yourself a copy from here


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joydip Kanjilal Joydip Kanjilal - Microsoft MVP (2007 to 2012), Author and Speaker with more than 20 years of experience. Authored more than 500 articles, 8 books and reviewed a dozen books.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, June 2, 2023 - 11:02:01 AM - JP Back To Top (91246)
Great. But do you know how to exploit this error message to the calling PS script so that it exit with RC 1 to the scheduler

Thursday, June 1, 2023 - 1:52:38 PM - JP Back To Top (91243)
Thanks for your article. But how to use this error to report it to the calling Power Shell script so that it exit 1 to scheduler

Wednesday, November 9, 2022 - 3:51:06 PM - Joydip Back To Top (90677)
Hi Greg,

I'll write a follow-up article in a couple of weeks. I should be able to restart writing articles here soon. Sorry for the delay in this. Thanks Tom and Greg for pointing this out!

Thanks,
Joydip

Wednesday, November 9, 2022 - 3:16:42 PM - Tom Wickerath Back To Top (90676)
Hi Greg,
Thanks--that's a good idea. I was just trying to prompt Joydip into writing that article he promised. :-)

Wednesday, November 9, 2022 - 9:42:10 AM - Greg Robidoux Back To Top (90674)
Hi Tom,

You could probably just adjust the code below in the CATCH block and instead of doing a SELECT you can do an INSERT into a log table that you create.

BEGIN TRY
Insert Into Categories(CategoryID, CategoryName, Description, Picture) Values (9, 'Test', 'Test Description', 'Test')
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [Error Message]
,ERROR_LINE() AS ErrorLine
,ERROR_NUMBER() AS [Error Number]
,ERROR_SEVERITY() AS [Error Severity]
,ERROR_STATE() AS [Error State]
END CATCH

Wednesday, November 9, 2022 - 9:40:06 AM - Greg Robidoux Back To Top (90673)
Hi Tom,

It doesn't look like Joydin ever wrote a follow-up article.

Thanks
Greg

Wednesday, November 9, 2022 - 12:02:58 AM - Tom Wickerath Back To Top (90672)
Hi Joydin,
You wrote, in the summary section: "You can even log errors that are caught inside the CATCH block – I’ll discuss on it and a few advanced features in a future article here."

Where can I find this article?

Thank you.














get free sql tips
agree to terms