Gracefully Handle Errors using TRY CATCH Logic in SQL Server Stored Procedures

By:   |   Updated: 2024-01-18   |   Comments (7)   |   Related: > Error Handling


Problem

Proper error handling is an integral part of any stable application. In SQL Server, you might encounter errors for several reasons, such as invalid data, inconsistencies in the data, and system issues. This article examines how to handle errors gracefully using stored procedures in SQL Server.

Solution

There are several ways to handle errors in SQL Server stored procedures, such as TRY/CATCH blocks, RAISERROR statements, and THROW statements. In this article, we'll discuss how you can handle errors in SQL Server stored procedures.

Error Handling in Stored Procedures in SQL Server

In SQL Server, you can take advantage of TRY…CATCH statements to handle errors. Before we examine how to handle exceptions in SQL Server stored procedures, let's quickly recap TRY…CATCH blocks and their syntax.

TRY…CATCH blocks are used in several programming languages to handle exceptions that can occur when your program is in execution. Statements that might throw an exception should be enclosed within the BEGIN TRY…END TRY blocks. Note: A TRY block should be before a CATCH block where you should write your error handling code.

Below is 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

After the BEGIN TRY...END TRY block, you should have a BEGIN CATCH...END CATCH block. If any statements inside the TRY block throw an exception, the control will be transferred to the CATCH block. If the statements inside the TRY don't throw any exception, the statements inside the CATCH block will not be executed, and the control will be transferred to the next statement after the END CATCH block.

Nested TRY...CATCH Blocks

It should be noted that TRY...CATCH blocks can be nested as well. So, you can have one or more TRY...CATCH blocks inside the parent TRY...CATCH block, as shown in the code snippet below.

Create Proc Try_Catch_Demo
As
BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'This statement will not be executed'
END TRY
BEGIN CATCH
    PRINT 'This is an error: ' + error_message()
    BEGIN TRY
       DECLARE @y int
       SELECT @y = 1/0
    END TRY
    BEGIN CATCH
       PRINT 'This is another error: ' + error_message()
    END CATCH
END CATCH

Retrieving Error Details

You can use the following functions inside the CATCH block to retrieve detailed information related to the exception:

  • ERROR_LINE()
  • ERROR_MESSAGE()
  • ERROR_PROCEDURE()
  • ERROR_NUMBER()
  • ERROR_SEVERITY()
  • ERROR_STATE()

You can learn more about these functions in an earlier article: Error Handling in SQL Server with Try Catch.

Working with TRY…CATCH blocks

Let us now examine how we can use TRY…CATCH blocks in real-life applications. Consider an order processing system with several database tables, such as Customer, Supplier, Product, Order, OrderDetail, etc.

Create a new database table named Product using the following script:

CREATE TABLE Product (
    Id Int Primary Key Identity(1,1),
    Name nvarchar(50) NOT NULL,
    Description nvarchar(100) NOT NULL
);

For brevity and simplicity, we will confine our discussion to the Product table here. Note the identity column in the Product table. We'll now attempt to insert a record into the Product table using a stored procedure.

To do this, create a stored procedure named InsertProduct with the following code:

CREATE PROCEDURE InsertProduct
@Id INT,  @Name NVARCHAR(50), @Description NVARCHAR(100)
AS
BEGIN
 BEGIN TRY
   Insert into Product (Id, Name, Description) Values (@Id, @Name, @Description);
 END TRY
 
 BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(1000);
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;
 
   SELECT
      @ErrorMessage = ERROR_MESSAGE(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState = ERROR_STATE();
 
   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH;
END;

Now execute the stored procedure and pass values to each of its parameters as shown below:

exec InsertProduct 1, 'HP Laptop', 'HP i9 Laptop with 32 GB RAM and SSD'

Since you cannot insert any value in an identity column of a table explicitly, the insert statement will fail with the following message:

The Insert statement fails since identity insert is turned On in the Product table

Storing Error Metadata in a Database Table

You can also log the error messages into a database table. The following code shows how to store the error metadata in a temporary table in SQL Server:

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 #ErrorLog;

You can now display the records of the ErrorLog database table using the following statement:

SELECT * FROM #ErrorLog;

Let us update the InsertProduct stored procedure to store error metadata in a database table. Here is the updated stored procedure InsertProduct we created earlier: 

CREATE PROCEDURE InsertProduct
@Id INT,  @Name NVARCHAR(50), @Description NVARCHAR(100)
AS
BEGIN
 BEGIN TRY
   Insert into Product (Id, Name, Description) Values (@Id, @Name, @Description);
 END TRY
 
 BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(1000);
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;
 
   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;
 
   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH;
SELECT * FROM #Error_Log;
END;

When you execute the stored procedure with the same parameters again, a temporary table named Error_Log will be created, and the error metadata will be stored there. Note the SELECT statement at the end of the stored procedure to retrieve the records of this temporary table. The image below shows the output upon execution of the InsertProduct stored procedure.

Displaying the records of the Error_Log database table

Using a Custom Database Table for Storing Error Metadata

You can also create a database table for storing error metadata per your requirements. The script below can be used to create a database table named ErrorLog for storing error metadata.

CREATE TABLE ErrorLog
(ErrorId INT IDENTITY(1, 1),
 ErrorNumber    INT,
 ErrorState     INT,
 ErrorLine      INT,
 ErrorSeverity  INT,
 ErrorProcedure VARCHAR(MAX),
 ErrorMessage   VARCHAR(MAX),
 ErrorDateTime  DATETIME)

The following script is a modified version of the InsertProduct stored procedure we created earlier to insert error metadata into a custom database table:

CREATE PROCEDURE InsertProduct
@Id INT,  @Name NVARCHAR(50), @Description NVARCHAR(100)
AS
BEGIN
 BEGIN TRY
   Insert into Product (Id, Name, Description) Values (@Id, @Name, @Description);
 END TRY
 
 BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(MAX);
   DECLARE @ErrorNumber INT;
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;
   DECLARE @ErrorLine INT;
   DECLARE @ErrorProcedure NVARCHAR(MAX);
   DECLARE @ErrorDateTime DateTime;
 
   SELECT
     @ErrorMessage = ERROR_MESSAGE(),
     @ErrorNumber = ERROR_NUMBER(),
     @ErrorSeverity = ERROR_SEVERITY(),
     @ErrorState = ERROR_STATE(),
     @ErrorLine = ERROR_LINE(),
     @ErrorProcedure = ERROR_PROCEDURE(),
     @ErrorDateTime = GETDATE();

   Insert into ErrorLog (ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorDateTime) 
   Values (@ErrorNumber, @ErrorState, @ErrorSeverity, @ErrorLine, @ErrorProcedure, @ErrorMessage, @ErrorDateTime)

   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
 END CATCH;
 
 SELECT * FROM ErrorLog;
END;

When you execute the updated version of the stored procedure InsertProduct, a record containing error metadata will be inserted into the ErrorLog database table, as seen in the image below.

The InsertProduct stored procedure inserts a record into the ErrorLog database table

Summary

Identifying, comprehending, and resolving issues is easier when the errors are logged and reported. By handling errors efficiently in your application, you can prevent data loss and application crashes, safeguard data integrity, and enhance the user experience. The built-in error-handling mechanism in SQL Server can help make your application reliable and fault-tolerant.

Next Steps

SQL Server provides built-in support for handling exceptions gracefully. You can use TRY...CATCH blocks when working with transactions, triggers, etc. I'll discuss more on exception handling in SQL Server in future articles.



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


Article Last Updated: 2024-01-18

Comments For This Article




Friday, February 16, 2024 - 8:58:19 AM - Joydip Kanjilal Back To Top (91980)
You might gain the best performance only by clustering the underlying table. Then, a direct query, a view, or a tvf will work just fine. However, the performance issues would creep in once you've a large dataset. I'll discuss these are several other interesting points related to performance in my future tips here.

Friday, February 16, 2024 - 8:44:15 AM - Joydip Kanjilal Back To Top (91979)
Yes, we can use table-valued functions, but unfortunately, there are performance drawbacks irrespective of whether we use inline or multi-statement table-valued functions. This is because table-valued functions cause portions of an execution plan to stay serial, i.e., they avoid parallelism. If we use multi-statement table-valued functions, performance can degrade even further unless proper optimization techniques/strategies have been used.

Friday, February 16, 2024 - 8:29:59 AM - Joydip Kanjilal Back To Top (91978)
Yes, ideally the Select statement should be inside the catch block but there is no harm if it is outside the try-catch block since the objective of the code is only to display the error metadata. So, if there is no errors recorded / saved into the database, this statement will not display any data.

Friday, February 16, 2024 - 8:15:11 AM - Joydip Kanjilal Back To Top (91977)
Good point. In a future article here, I will discuss try-catch with transaction handling in detail. Thanks!

Monday, February 12, 2024 - 2:13:08 AM - animesh saxena Back To Top (91937)
The Select from the temp table must be in the catch block.....

Wednesday, January 31, 2024 - 4:49:06 AM - GK Back To Top (91902)
I'm seen some situations using try-catch with transactions.
On error the catch block rolls back the transaction (for the case where more than 1 table is being affected you want to use a transaction).

begin try
begin transaction;
...
commit transaction;
end try
begin catch
rollback transaction;

declare @message nvarchar(max);
set @message = error_message();
end catch

The weird thing here is that you lose the original raised error message if the rollback fails (like having a nested transaction that rolls back in another procedure). The error message will actually be from the transaction already having been rolled back.

Always capture the message, etc, first before doing the rollback.
The worst thing was that the try-catch setup was done as a snippet. Now there are hundreds of procedures that need altering. :P

Thursday, January 18, 2024 - 9:18:05 AM - Richard Back To Top (91860)
I really like the idea of the error table to capture the errors internally. I do have a suggestion through. I turned the various error function calls into a table valued function. This makes the Catch block much more elegant and easier to code. Something like so...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Begin Catch
Insert Into ErrorLog
( ErrorNumber
, ErrorState
, ErrorSeverity
, ErrorLine
, ErrorProcedure
, ErrorMessage
, ErrorDateTime
)
Select ErrorNumber
, ErrorState
, ErrorSeverity
, ErrorLine
, ErrorProcedure
, ErrorMessage
, getDate ()
From master.dbo.fn_GetErrorInfo ();

Raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
End Catch;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I added the "getDate ()" call because I don't have that in my function.

Thanks and have a great day.














get free sql tips
agree to terms