Error Handling in SQL Server
Learn how to detect, handle and manage errors in SQL Server using TRY...CATCH, error functions, THROW and RAISERROR.
1. What is Error Handling?
Error Handling is the process of detecting and managing errors that occur during the execution of SQL statements.
Without proper error handling, an unexpected error can stop the execution of a SQL batch, stored procedure or transaction.
SQL Server provides the TRY...CATCH mechanism to handle runtime errors.
2. Why is Error Handling Important?
- It prevents unexpected termination of SQL operations.
- It provides useful information about errors.
- It allows applications to respond properly to errors.
- It helps maintain database consistency.
- It is especially important when working with transactions.
3. TRY...CATCH
SQL Server uses the TRY...CATCH construct to handle runtime errors.
Statements that may generate an error are placed inside the TRY block.
If an error occurs, SQL Server transfers control to the CATCH block.
BEGIN TRY
-- SQL statements
END TRY
BEGIN CATCH
-- Error handling statements
END CATCH;
4. Simple TRY...CATCH Example
The following example generates a divide-by-zero error. The error is handled by the CATCH block.
BEGIN TRY
SELECT 10 / 0 AS Result;
END TRY
BEGIN CATCH
PRINT 'An error occurred.';
END CATCH;
- TRY contains the statement that may generate an error.
- 10 / 0 generates a divide-by-zero error.
- Control moves to the CATCH block.
- The error message is printed.
5. ERROR_NUMBER()
ERROR_NUMBER() returns the error number generated by SQL Server.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
6. ERROR_MESSAGE()
ERROR_MESSAGE() returns the complete error message generated by SQL Server.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
7. ERROR_SEVERITY()
ERROR_SEVERITY() returns the severity level of the error.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
8. ERROR_STATE()
ERROR_STATE() returns the state number associated with the error.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_STATE() AS ErrorState;
END CATCH;
9. ERROR_LINE()
ERROR_LINE() returns the line number at which the error occurred.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
10. ERROR_PROCEDURE()
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;
11. Retrieve Complete Error Information
We can use multiple error functions together to retrieve detailed information about an error.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine;
END CATCH;
12. THROW Statement
The THROW statement can be used to generate an exception or re-throw an existing error.
It is commonly used inside a CATCH block when the application needs to pass the error to another layer.
BEGIN TRY
SELECT 10 / 0;
END TRY
BEGIN CATCH
THROW;
END CATCH;
13. Creating a Custom Error using THROW
THROW 50001,
'Employee salary cannot be negative.',
1;
User-defined error numbers used with THROW should be greater than or equal to 50000.
14. RAISERROR
RAISERROR can be used to generate a custom error message.
RAISERROR(
'Something went wrong.',
16,
1
);
15. Error Handling with Transactions
TRY...CATCH is very important when working with database transactions.
If an error occurs after a transaction starts, the transaction can be rolled back.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 1000
WHERE EmployeeId = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
16. Practical Error Handling Example
The following example demonstrates a complete error handling pattern.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 5000
WHERE EmployeeId = 101;
IF @ROWCOUNT = 0
BEGIN
THROW 50001,
'Employee was not found.',
1;
END;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
Important Points
- Use TRY...CATCH to handle runtime errors.
- Use ERROR_MESSAGE() to get the error message.
- Use ERROR_NUMBER() to get the error number.
- Use ERROR_LINE() to identify the error line.
- Use ERROR_PROCEDURE() to identify the procedure or trigger.
- Use THROW to generate or re-throw errors.
- When using transactions, check @TRANCOUNT before rolling back.
- Proper error handling helps maintain database consistency.
Summary
Error Handling provides a structured way to manage errors in SQL Server.
The TRY...CATCH mechanism allows SQL Server applications to detect and handle runtime errors.
Functions such as ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE() and ERROR_PROCEDURE() provide useful error information.