As a SQL developer working with Microsoft SQL Server, you'll inevitably encounter runtime errors—whether it's a divide-by-zero exception, a constraint violation, or a conversion failure. Without proper error handling with TRY…CATCH in T-SQL, these errors can crash your stored procedures, leave transactions incomplete, and create nightmare debugging scenarios.
The TRY…CATCH construct in SQL Server provides a structured, reliable way to handle errors gracefully, similar to exception handling in languages like C# or Java. Instead of letting errors terminate your code execution, you can catch them, log them, and respond appropriately—all while maintaining data integrity.
In this comprehensive guide, I'll walk you through everything you need to know about implementing TRY CATCH in SQL Server, using real-world examples from the WideWorldImporters sample database. Whether you're building stored procedures for order processing or managing inventory updates, mastering T-SQL error handling will make your code more robust and production-ready.
Understanding the TRY…CATCH Structure
The TRY…CATCH block in T-SQL follows a straightforward pattern:
BEGIN TRY
-- Code that might generate an error
-- Your normal T-SQL statements go here
END TRY
BEGIN CATCH
-- Code to handle the error
-- Error information and recovery logic
END CATCHWhen SQL Server executes code inside the BEGIN TRY block, it monitors for errors. If an error occurs with a severity level of 11 or higher, execution immediately jumps to the BEGIN CATCH block. This allows you to handle the error gracefully instead of terminating the batch.
Step-by-Step Guide: Implementing Error Handling in Real Scenarios
Basic TRY…CATCH Example with WideWorldImporters
Let's start with a simple scenario: updating stock quantity for an item in the Warehouse.StockItemHoldings table.
USE WideWorldImporters;
GO
-- Basic error handling example
BEGIN TRY
UPDATE Warehouse.StockItemHoldings
SET QuantityOnHand = QuantityOnHand - 1000
WHERE StockItemID = 1;
PRINT 'Stock quantity updated successfully';
END TRY
BEGIN CATCH
PRINT 'An error occurred during the update operation';
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCHThis basic pattern protects your code from unexpected failures. Now let's explore the error functions available in the CATCH block.
Using Error Functions to Capture Details
SQL Server provides several built-in functions that work exclusively within the CATCH block to give you detailed error information:
BEGIN TRY
-- Attempting to insert a duplicate customer
INSERT INTO Sales.Customers (CustomerID, CustomerName, BillToCustomerID,
CustomerCategoryID, PrimaryContactPersonID,
DeliveryMethodID, DeliveryCityID, PostalCityID,
LastEditedBy)
VALUES (1, 'Duplicate Customer Test', 1, 3, 1001, 3, 19586, 19586, 1);
END TRY
BEGIN CATCH
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 CATCHKey Error Functions:
- ERROR_NUMBER(): Returns the error number (e.g., 2627 for primary key violation)
- ERROR_MESSAGE(): Returns the complete error message text
- ERROR_SEVERITY(): Returns the severity level (11-25)
- ERROR_STATE(): Returns the error state number
- ERROR_LINE(): Returns the line number where the error occurred
- ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error occurred
Transaction Management with TRY…CATCH
One of the most critical uses of TRY CATCH in SQL Server is managing transactions. When processing orders or financial data, you need to ensure all-or-nothing execution. Here's a realistic example processing a sales order:
CREATE OR ALTER PROCEDURE Sales.usp_ProcessCustomerOrder
@CustomerID INT,
@OrderDate DATE,
@StockItemID INT,
@Quantity INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderID INT;
DECLARE @ErrorMessage NVARCHAR(4000);
BEGIN TRY
BEGIN TRANSACTION;
-- Insert the order header
INSERT INTO Sales.Orders (CustomerID, SalespersonPersonID, PickedByPersonID,
ContactPersonID, BackorderOrderID, OrderDate,
ExpectedDeliveryDate, CustomerPurchaseOrderNumber,
IsUndersupplyBackordered, LastEditedBy, LastEditedWhen)
VALUES (@CustomerID, 2, NULL, 1001, NULL, @OrderDate,
DATEADD(DAY, 7, @OrderDate), 'PO-' + CAST(@CustomerID AS VARCHAR(10)),
0, 1, GETDATE());
SET @OrderID = SCOPE_IDENTITY();
-- Insert the order line
INSERT INTO Sales.OrderLines (OrderID, StockItemID, Description, PackageTypeID,
Quantity, UnitPrice, TaxRate, PickedQuantity,
LastEditedBy, LastEditedWhen)
SELECT @OrderID, @StockItemID, StockItemName, 7, @Quantity,
UnitPrice, TaxRate, 0, 1, GETDATE()
FROM Warehouse.StockItems
WHERE StockItemID = @StockItemID;
-- Update inventory
UPDATE Warehouse.StockItemHoldings
SET QuantityOnHand = QuantityOnHand - @Quantity
WHERE StockItemID = @StockItemID AND QuantityOnHand >= @Quantity;
-- Check if update affected any rows (enough inventory)
IF @@ROWCOUNT = 0
BEGIN
THROW 50001, 'Insufficient inventory for this order', 1;
END
COMMIT TRANSACTION;
PRINT 'Order processed successfully. Order ID: ' + CAST(@OrderID AS VARCHAR(10));
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @ErrorMessage = 'Error processing order: ' + ERROR_MESSAGE();
-- Log error to a custom error table (example)
PRINT @ErrorMessage;
-- Re-throw the error to the calling application
THROW;
END CATCH
END
GOThis stored procedure demonstrates professional error handling in T-SQL:
- Transaction wrapping: Ensures data consistency
- Conditional rollback: Only rolls back if a transaction is active
- Custom error throwing: Uses
THROWfor business logic violations - Error propagation: Re-throws errors to inform the calling application
Testing the Error Handling
-- Test with valid data
EXEC Sales.usp_ProcessCustomerOrder
@CustomerID = 1,
@OrderDate = '2024-02-08',
@StockItemID = 1,
@Quantity = 5;
-- Test with insufficient inventory (will trigger our custom error)
EXEC Sales.usp_ProcessCustomerOrder
@CustomerID = 1,
@OrderDate = '2024-02-08',
@StockItemID = 1,
@Quantity = 999999;Nested TRY…CATCH Blocks
Sometimes you need different error handling strategies at different levels. Here's an example with nested error handling when updating supplier information:
CREATE OR ALTER PROCEDURE Purchasing.usp_UpdateSupplierWithAudit
@SupplierID INT,
@SupplierName NVARCHAR(100),
@UpdatedBy INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Outer TRY: Main business logic
BEGIN TRANSACTION;
UPDATE Purchasing.Suppliers
SET SupplierName = @SupplierName,
LastEditedBy = @UpdatedBy,
LastEditedWhen = GETDATE()
WHERE SupplierID = @SupplierID;
IF @@ROWCOUNT = 0
BEGIN
THROW 50002, 'Supplier ID not found', 1;
END
-- Attempt to log the change (nested TRY)
BEGIN TRY
INSERT INTO Application.People (FullName, IsPermittedToLogon,
LogonName, IsExternalLogonProvider,
IsSystemUser, IsEmployee, IsSalesperson,
LastEditedBy)
VALUES ('Audit Log Entry', 0, NULL, 0, 0, 0, 0, @UpdatedBy);
END TRY
BEGIN CATCH
-- Log audit failure, but don't fail the whole operation
PRINT 'Warning: Audit logging failed - ' + ERROR_MESSAGE();
END CATCH
COMMIT TRANSACTION;
PRINT 'Supplier updated successfully';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH
END
GOTips and Best Practices for T-SQL Error Handling
1. Always Check @@TRANCOUNT Before Rollback
Before rolling back a transaction, verify that one is actually active:
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCHThis prevents the error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" which can mask your original error.
2. Use THROW Instead of RAISERROR for Modern Code
For SQL Server 2012 and later, prefer THROW over RAISERROR:
-- Modern approach
IF @Quantity <= 0
THROW 50003, 'Quantity must be greater than zero', 1;
-- Legacy approach (still valid)
IF @Quantity <= 0
RAISERROR('Quantity must be greater than zero', 16, 1);THROW is simpler and automatically re-throws the original error when used without parameters.
3. Create a Centralized Error Logging Procedure
Instead of handling errors ad-hoc, create a reusable error logging procedure:
CREATE OR ALTER PROCEDURE dbo.usp_LogError
@ErrorProcedure NVARCHAR(128) = NULL
AS
BEGIN
SET NOCOUNT ON;
-- In production, insert into an error log table
SELECT
GETDATE() AS ErrorDateTime,
ISNULL(@ErrorProcedure, ERROR_PROCEDURE()) AS ErrorProcedure,
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState;
END
GOThen call it from your CATCH blocks:
BEGIN CATCH
EXEC dbo.usp_LogError @ErrorProcedure = 'Sales.usp_ProcessCustomerOrder';
THROW;
END CATCH4. Don't Swallow Errors Silently
One of the worst mistakes in error handling is catching errors without taking any action:
-- BAD: Silent error swallowing
BEGIN TRY
-- Critical operation
END TRY
BEGIN CATCH
-- Nothing here - error disappears!
END CATCH
-- GOOD: At minimum, log or re-throw
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
THROW;
END CATCHCommon Mistakes to Avoid
Mistake 1: Forgetting NOCOUNT ON in Error Handling Procedures
Without SET NOCOUNT ON, row count messages can interfere with error handling logic in calling applications.
Mistake 2: Not Handling Deadlocks Specifically
Deadlocks (error 1205) often need special retry logic:
DECLARE @Retries INT = 3;
DECLARE @RetryCount INT = 0;
WHILE @RetryCount < @Retries
BEGIN
BEGIN TRY
-- Your transaction code here
UPDATE Sales.Customers
SET CreditLimit = CreditLimit + 1000
WHERE CustomerID = 1;
BREAK; -- Success, exit loop
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
SET @RetryCount = @RetryCount + 1;
WAITFOR DELAY '00:00:01'; -- Wait before retry
IF @RetryCount >= @Retries
THROW;
END
ELSE
THROW; -- Not a deadlock, re-throw immediately
END CATCH
ENDMistake 3: Using TRY…CATCH for Expected Conditions
Don't use error handling for normal business logic:
-- BAD: Using error handling for expected conditions
BEGIN TRY
SELECT 1/0; -- Expected to fail
END TRY
BEGIN CATCH
PRINT 'Cannot divide by zero';
END CATCH
-- GOOD: Use conditional logic
IF @Divisor <> 0
SELECT @Result = @Numerator / @Divisor;
ELSE
PRINT 'Cannot divide by zero';Summary: Key Takeaways
Error handling with TRY…CATCH in T-SQL is essential for building robust, production-ready database solutions. Here are the critical points to remember:
- Structure: TRY…CATCH blocks provide structured exception handling similar to modern programming languages
- Error Functions: Use ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), and others to capture detailed error information
- Transaction Safety: Always check @@TRANCOUNT before rolling back transactions
- Modern Syntax: Prefer THROW over RAISERROR in SQL Server 2012+
- Logging: Implement centralized error logging for easier debugging and monitoring
- Don't Suppress: Never silently swallow errors—always log or re-throw
- Business Logic: Use conditional statements for expected conditions, not error handling
By implementing these T-SQL error handling best practices, you'll create stored procedures and scripts that gracefully handle failures, maintain data integrity, and provide meaningful feedback for debugging and monitoring.
Frequently Asked Questions
Q: What errors cannot be caught by TRY…CATCH in SQL Server?
A: TRY…CATCH cannot catch errors with severity 10 or lower (informational messages), compile errors in the same execution scope, or severe errors like out-of-memory conditions (severity 20-25) that terminate the connection. Additionally, syntax errors in the TRY block itself cannot be caught—the code must compile successfully first.
Q: Should I use RAISERROR or THROW in my CATCH blocks?
A: For SQL Server 2012 and later, use THROW. It's simpler (fewer parameters), automatically includes the original error number when re-throwing, and doesn't require defining custom error messages. Use RAISERROR only for backward compatibility with SQL Server 2008 R2 and earlier, or when you specifically need its additional formatting capabilities.
Q: How do I return error information to my application from a stored procedure?
A: You have several options: (1) Use THROW or RAISERROR to propagate the error to the calling application, which can catch it in application code; (2) Use output parameters to return error codes and messages; (3) Use RETURN to send back an error code; or (4) SELECT error information as a result set. For most scenarios, using THROW to propagate errors and handling them in your application's data access layer is the cleanest approach.