SQL Try Catch for Effective Error Handling

Problem

Adding error handling to SQL Server stored procedures to prevent failures or unexpected error messages is important when developing code. In this article, we are going to look at the basics of the TRY…CATCH code that can be used to catch and gracefully handle errors.

Solution

To handle errors in SQL Server code we can use TRY…CATCH processing. Basically what happens is when an error occurs, the processing in the TRY block stops and processing is then picked up in the CATCH block. This allows you as a developer to gracefully handle the failure and determine what should be done.

TRY CATCH Example

The following illustrates an example of how to use TRY CACTH.

Step 1

Let’s create a simple stored procedure that try’s to SELECT data from a table that does not exist.

CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO

The above code runs and creates the stored procedure.

Step 2

Now let’s execute the stored procedure to see what happens.

EXECUTE usp_ExampleProc

After running, we get this error message and processing stops.

Msg 208, Level 16, State 1, Procedure usp_ExampleProc, Line 3 [Batch Start Line 6]
Invalid object name 'NonexistentTable'.

Step 3

To better handle the error we can use TRY CATCH to catch the error and then do something in the CATCH section. In the below code we are returning the error number and error message. Also, the stored procedure does not fail and completes successfully.

BEGIN TRY
   EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
   SELECT
   ERROR_NUMBER() as ErrorNumber,
   ERROR_MESSAGE() as ErrorMessage;
END CATCH;

This is the message that is returned:

208 Invalid object name 'NonexistentTable'.

Step 4

We could also do the following and not include anything in the CATCH section and the stored procedure still completes successfully.

BEGIN TRY
   EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
END CATCH;

Code Explained

Here is a summary:

As you can see from the above code and output that when we create the stored procedure for a non-existent table in Step 1 the procedure creates without a problem.
If we run the stored procedure using the code in Step 2, we get an error message that the object does not exist.
If we run the stored procedure using the code in Step 3, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.
To further illustrate this in Step 4 the stored procedure is run, the error is caught in the CATCH block, but we are not doing anything to process the error. Normally you would have something happen, but this shows that you don’t have to have any code in the CATCH block.

TRY CATCH Values

The values that can be retrieved from the error are also much more detailed, then what you could get with the standard errors. Below is a list of the data that can be retrieved when an error occurs.

ERROR_NUMBER() – returns the number of the error.
ERROR_SEVERITY() – returns the severity.
ERROR_STATE() – returns the error state number.
ERROR_PROCEDURE() – returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() – returns the line number inside the routine that caused the error.
ERROR_MESSAGE() – returns the complete text of the error message.

Nested TRY CATCH

Another nice thing about the TRY…CATCH processing is that you can nest or have multiple TRY…CATCH blocks in your code. The following although not very practical illustrates how the error is caught and then processing continues and the error is caught again and processing continues again.

BEGIN TRY
   BEGIN TRY
      EXECUTE usp_ExampleProc
   END TRY
   BEGIN CATCH
      SELECT
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity;
   END CATCH;
 
   EXECUTE usp_ExampleProc
 
END TRY
 
BEGIN CATCH
   SELECT
   ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

The above code catches both errors and reports on them both.

Next Steps

Take a look at how the TRY…CATCH processing can boost your SQL Server error handling
Look for ways how you are currently catching and processing errors and how this new option can significantly improve your application and user experience.
Related articles:

Greg Robidoux

Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.

SQL Server Try Catch Exception Handling