SQL Server TRY CATCH, RAISERROR and THROW for Error Handling

By:   |   Updated: 2024-05-24   |   Comments (1)   |   Related: > Error Handling


Problem

SQL Server application errors can occur for several reasons such as erroneous data, data inconsistencies, system failures, or other errors. In this tutorial, we'll examine how to handle errors in SQL Server using TRY...CATCH, RAISERROR and THROW.

Solution

SQL Server errors can be handled several ways in T-SQL logic, such as TRY…CATCH blocks, RAISERROR statements and THROW statements. Each option has various pros and cons. Let's take a look at examples of each option.

SQL Server TRY...CATCH

In SQL Server you can take advantage of TRY...CATCH statements to handle errors.

Place the application code inside the TRY block and if there are any errors in the TRY block, the code inside the CATCH block then gets executed to capture and gracefully handle the errors.

Here's example code that illustrates this:

BEGIN TRY  
   -- Write statements here - if errors CATCH block code is run
END TRY
BEGIN CATCH  
   -- Write statements here to handle exception
END CATCH

You specify the statements that might throw exceptions inside a TRY block. If any of the statements within the TRY block encounters an error, the control flow is transferred to the first statement inside the CATCH block.

On the contrary, if none of the statements inside a TRY block raise an exception, the control doesn't move inside the CATCH block. Instead, the first statement, if any, following the END CATCH statement executes.

SQL SERVER RAISERRROR

You can take advantage of the RAISEERROR statement in SQL Server to generate custom messages and return them to the application.

RAISERROR [ error_message, error_severity, error_state ];

Here is an example code snippet below:

BEGIN TRY
    RAISERROR ('An error occurred in the TRY block.', 16, 1);
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(2048),
            @ErrorSeverity INT,
            @ErrorState INT;
 
    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

Above, in the TRY block we issue a RAISERRROR with a set message. In the CATCH block, the RAISERROR sends the actual error that was encountered.

SQL SERVER THROW

The THROW statement in SQL Server is used to raise an exception.

The following shows the syntax of the THROW statement in SQL Server:

THROW [ error_number, error_message, error_state ];

The code snippet below shows how to use the THROW statement to raise the error caught in the CATCH block.

BEGIN TRY
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    PRINT('The error is raised once again');
    THROW;
END CATCH

Built-In Functions in SQL Server for Handling Errors

SQL Server supports several built-in functions to handle errors when they occur. The following is the list of built-in functions for handling errors:

  • ERROR_NUMBER(): Retrieve the error number caused by the last statement executed.
  • ERROR_SEVERITY(): Return the severity of the error that occurred.
  • ERROR_MESSAGE(): Return detailed description of error message.
  • ERROR_STATE(): Return the error state.
  • ERROR_LINE(): Returns the line number in the code where error has occurred.
  • ERROR_PROCEDURE(): Return the name of the trigger or stored procedure where the error might have occurred.

SQL Server Error Handling for User-Defined Functions

Error handling capabilities in SQL Server functions are considerably limited compared to stored procedures because you can't use TRY...CATCH blocks inside a UDF. Instead, you can follow specific programming best practices to ensure your UDFs in SQL Server are devoid of errors.

One way to make your SQL Server UDF is error-free is by validating inputs to ensure that the input data satisfies the desired values before proceeding with the logic of the function. You can also return special codes that indicate error conditions and then handle them in the calling code. That said, since error handling capabilities in stored procedures are more robust, handling errors in your stored procedures that call the UFDs is a good practice.

In this section, we'll create a simple user-defined function and then examine how to invoke it from a stored procedure, which, in turn, will contain the necessary error-handling code.

Consider the following SQL Server UDF:

CREATE FUNCTION DivideIntegers
(
   @x int, @y int
)
RETURNS int
AS
BEGIN 
  DECLARE @r int
  SELECT @r = @x / @y
  RETURN @r
END
GO

The UDF DivideIntegers accepts two integers as parameters, divides the first by the second, and returns the result. Note: This function doesn't handle errors, i.e., it doesn't have any mechanism to handle errors using TRY…CATCH blocks.

The following code snippet shows a simple stored procedure that can be used to invoke the UDF:

CREATE PROCEDURE sp_InvokeDivideIntegerUDF 
(
      @x int, @y int
)
AS
BEGIN 
   BEGIN TRY
      DECLARE @r int
      SELECT [dbo].[DivideIntegers] (@x, @y)
   END TRY
 
   BEGIN CATCH
      SELECT 'Error occurred...'
   END CATCH
END
GO

SQL Server Error Handling for Triggers

Triggers are a specific type of stored procedure designed to automatically execute or "fire" in response to specific events occurring within the database, such as actions that can lead to errors. You can use triggers to handle errors effectively in such scenarios.

In SQL Server, you can use TRY…CATCH blocks inside triggers.

The following code illustrates a trigger that uses TRY…CATCH blocks to handle errors:

CREATE TRIGGER MyTrigger
ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
 
    BEGIN TRY
      BEGIN TRANSACTION

      INSERT INTO dbo.Transaction_History (Transaction_Description, Transaction_Status) 
                                   values ('1 record inserted in Customers table.', 1/0);
      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
      INSERT INTO dbo.ErrorLog (Error_Description) VALUES (ERROR_MESSAGE());
    END CATCH
END;

Summary

In SQL Server, error handling is a mechanism that helps maintain data integrity, enhance the usability of the database system, and simplifies troubleshooting efforts. You can leverage SQL Server's error handling capabilities to proactively identify and address potential issues, provide informative feedback to users, and ensure a robust and consistent database environment. However, while you can use TRY…CATCH blocks inside stored procedures and triggers, you can't use them inside UDFs.

Next Steps

Check out these related 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-05-24

Comments For This Article




Friday, June 21, 2024 - 1:21:31 PM - Brad Back To Top (92335)
I would add that if you are executing a stored procedure in a SQL Agent job and want to stop the job when an error is encountered, the severity needs to be at least 11 to stop the job.














get free sql tips
agree to terms