SQL - Error Handling (TRY...CATCH)
SQL Server's TRY...CATCH construct wraps potentially error-prone code in a TRY block, transferring control to the CATCH block when errors occur. This prevents automatic termination and allows...
Key Insights
- SQL Server’s TRY…CATCH blocks provide structured exception handling similar to modern programming languages, allowing you to gracefully handle errors and maintain transaction integrity instead of letting errors propagate to the application layer.
- The CATCH block exposes critical error information through functions like ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_STATE(), enabling precise error diagnosis and appropriate recovery actions based on specific error conditions.
- Proper error handling requires understanding error severity levels, implementing nested TRY…CATCH blocks for complex scenarios, and designing rollback strategies that preserve data consistency while logging failures for troubleshooting.
Basic TRY…CATCH Structure
SQL Server’s TRY…CATCH construct wraps potentially error-prone code in a TRY block, transferring control to the CATCH block when errors occur. This prevents automatic termination and allows controlled error handling.
BEGIN TRY
-- Potentially problematic code
DECLARE @Result INT;
SET @Result = 10 / 0; -- Division by zero
SELECT @Result AS Calculation;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH
The CATCH block only executes when an error with severity 11-20 occurs within the TRY block. Errors with severity 10 or lower are warnings and don’t trigger the CATCH block.
Transaction Management with Error Handling
Combining TRY…CATCH with transactions ensures atomicity. When errors occur, you can roll back partial changes to maintain data consistency.
BEGIN TRY
BEGIN TRANSACTION;
-- Insert order header
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (1001, GETDATE(), 0);
DECLARE @OrderID INT = SCOPE_IDENTITY();
-- Insert order details
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES
(@OrderID, 501, 2, 29.99),
(@OrderID, 502, 1, 49.99);
-- Update inventory
UPDATE Products
SET StockQuantity = StockQuantity - 2
WHERE ProductID = 501;
UPDATE Products
SET StockQuantity = StockQuantity - 1
WHERE ProductID = 502;
-- Update order total
UPDATE Orders
SET TotalAmount = (SELECT SUM(Quantity * UnitPrice)
FROM OrderDetails
WHERE OrderID = @OrderID)
WHERE OrderID = @OrderID;
COMMIT TRANSACTION;
SELECT 'Order created successfully' AS Result;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
The @@TRANCOUNT check ensures you only roll back if a transaction is active, preventing errors when ROLLBACK is called outside a transaction context.
Error Logging Pattern
Production systems require persistent error logging for troubleshooting. Create an error log table and populate it within the CATCH block.
-- Create error log table
CREATE TABLE ErrorLog (
ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,
ErrorNumber INT,
ErrorSeverity INT,
ErrorState INT,
ErrorProcedure NVARCHAR(128),
ErrorLine INT,
ErrorMessage NVARCHAR(4000),
ErrorDateTime DATETIME DEFAULT GETDATE(),
UserName NVARCHAR(128) DEFAULT SUSER_SNAME()
);
GO
-- Stored procedure with error logging
CREATE PROCEDURE usp_UpdateProductPrice
@ProductID INT,
@NewPrice DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate input
IF @NewPrice <= 0
THROW 50001, 'Price must be greater than zero', 1;
IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductID)
THROW 50002, 'Product not found', 1;
-- Update price
UPDATE Products
SET UnitPrice = @NewPrice,
ModifiedDate = GETDATE()
WHERE ProductID = @ProductID;
-- Log price change
INSERT INTO PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
SELECT @ProductID, UnitPrice, @NewPrice, GETDATE()
FROM Products
WHERE ProductID = @ProductID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Log error
INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState,
ErrorProcedure, ErrorLine, ErrorMessage)
VALUES (
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
);
-- Re-throw error
THROW;
END CATCH
END
GO
Using THROW without parameters re-raises the original error, preserving the error number and message for the calling application.
Nested TRY…CATCH Blocks
Complex operations may require multiple levels of error handling. Inner CATCH blocks can handle specific errors while outer blocks manage overall transaction logic.
CREATE PROCEDURE usp_ProcessBatchOrders
@BatchID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProcessedCount INT = 0;
DECLARE @ErrorCount INT = 0;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @OrderID INT;
DECLARE order_cursor CURSOR FOR
SELECT OrderID FROM BatchOrders WHERE BatchID = @BatchID;
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Inner TRY for individual order processing
BEGIN TRY
EXEC usp_ProcessSingleOrder @OrderID;
SET @ProcessedCount += 1;
END TRY
BEGIN CATCH
-- Log individual order failure but continue processing
INSERT INTO OrderProcessingErrors (OrderID, ErrorMessage, ErrorDate)
VALUES (@OrderID, ERROR_MESSAGE(), GETDATE());
SET @ErrorCount += 1;
END CATCH
FETCH NEXT FROM order_cursor INTO @OrderID;
END
CLOSE order_cursor;
DEALLOCATE order_cursor;
-- Update batch status
UPDATE Batches
SET ProcessedCount = @ProcessedCount,
ErrorCount = @ErrorCount,
ProcessedDate = GETDATE()
WHERE BatchID = @BatchID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
IF CURSOR_STATUS('local', 'order_cursor') >= 0
BEGIN
CLOSE order_cursor;
DEALLOCATE order_cursor;
END
THROW;
END CATCH
END
GO
Handling Specific Error Types
Different errors require different responses. Use conditional logic in the CATCH block to implement error-specific handling strategies.
CREATE PROCEDURE usp_SafeInsertCustomer
@CustomerName NVARCHAR(100),
@Email NVARCHAR(100),
@Phone NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO Customers (CustomerName, Email, Phone, CreatedDate)
VALUES (@CustomerName, @Email, @Phone, GETDATE());
SELECT SCOPE_IDENTITY() AS NewCustomerID;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
-- Handle specific error types
IF @ErrorNumber = 2627 OR @ErrorNumber = 2601 -- Unique constraint violation
BEGIN
SELECT
'DUPLICATE' AS ErrorType,
'A customer with this email already exists' AS Message,
@Email AS ConflictingValue;
RETURN;
END
ELSE IF @ErrorNumber = 547 -- Foreign key violation
BEGIN
SELECT
'INVALID_REFERENCE' AS ErrorType,
'Referenced record does not exist' AS Message;
RETURN;
END
ELSE IF @ErrorNumber = 8152 -- String truncation
BEGIN
SELECT
'DATA_TOO_LONG' AS ErrorType,
'One or more values exceed maximum length' AS Message;
RETURN;
END
ELSE -- Unexpected error
BEGIN
-- Log and re-throw
INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine)
VALUES (@ErrorNumber, @ErrorMessage, ERROR_PROCEDURE(), ERROR_LINE());
THROW;
END
END CATCH
END
GO
Deadlock Retry Logic
Deadlocks (error 1205) are transient and often succeed on retry. Implement automatic retry logic for better resilience.
CREATE PROCEDURE usp_UpdateInventoryWithRetry
@ProductID INT,
@QuantityChange INT,
@MaxRetries INT = 3
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RetryCount INT = 0;
DECLARE @Success BIT = 0;
WHILE @RetryCount < @MaxRetries AND @Success = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Products WITH (UPDLOCK)
SET StockQuantity = StockQuantity + @QuantityChange,
ModifiedDate = GETDATE()
WHERE ProductID = @ProductID;
IF @@ROWCOUNT = 0
THROW 50003, 'Product not found', 1;
COMMIT TRANSACTION;
SET @Success = 1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
SET @RetryCount += 1;
IF @RetryCount < @MaxRetries
BEGIN
WAITFOR DELAY '00:00:00.100'; -- Wait 100ms before retry
CONTINUE;
END
ELSE
BEGIN
THROW 50004, 'Operation failed after maximum retries due to deadlock', 1;
END
END
ELSE
BEGIN
THROW; -- Re-throw non-deadlock errors immediately
END
END CATCH
END
SELECT @Success AS Success, @RetryCount AS RetryAttempts;
END
GO
This pattern exponentially backs off with WAITFOR DELAY, giving competing transactions time to complete before retrying.