Introduction

SQL Server provides a great option to handle the errors in Transact-SQL code. In case anything goes wrong, we have an option to handle those errors and get notified or details about the error in SQL. It is very easy to handle the errors or exceptions in the SQL server. In this blog, we will see how to handle the errors in SQL Server stored procedures using Try...Catch statement.

Handling errors using TRY…CATCH Statement

We can use Try...Catch statement to handle the errors in SQL Server stored procedures. Here is the syntax for Try...Catch statement.

BEGIN TRY  
     -- SQL code here
END TRY  
BEGIN CATCH  
     -- This code will be executed when an error occurs in the TRY block.
END CATCH

If you want to monitor the errors or handle the errors for your SQL statement then you have to place your code inside the BEGIN TRY and END TRY. If anything goes wrong or an error occurs in your statement then the execution will be shifted into BEGIN CATCH block and start to execute the statements in the CATCH block. We can write the code here to catch the error or log the detailed error for easy resolution.

Example:

In the below example, SELECT 1/0 generates the divide by zero error inside the TRY block. If an error occurs in TRY block then immediately the execution stops in TRY and transferred to the CATCH block and starts executing the statement line by line in the CATCH block.

CREATE PROCEDURE dbo.TryCatchTest
AS
BEGIN TRY
    SELECT 1/0  -- Generate a divide-by-zero error
                -- You can use your SQL Statements here to track the errors
END TRY
BEGIN CATCH
    -- Get the error details or Instead of the below statement you can log the error in a table.
    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;
END CATCH

Output:

The CATCH block catches the error and returns as a table as shown below,