By: Joydip Kanjilal | 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 RAISERROR to Better Handle Errors
- Raising Exceptions and Error Handling with SQL Server THROW
- Gracefully Handle Errors using TRY CATCH Logic in SQL Server Stored Procedures
About the author
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
