SQL Server TRY...CATCH Error Handling With Transaction Rollback Example

 

Introduction to Error Handling in SQL Server

Proper error handling is crucial for building robust and reliable database applications. SQL Server's TRY...CATCH construct provides a modern, structured way to handle errors, replacing the older @@ERROR-based approach. When combined with transactions, it ensures data consistency even when errors occur.

1. Understanding TRY...CATCH Basics

Basic Syntax

sql

BEGIN TRY
    -- Code that might cause an error
    SELECT 1/0; -- Division by zero error
END TRY
BEGIN CATCH
    -- Error handling code
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

2. Setting Up the Sample Environment

sql

-- Create sample tables
CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Balance DECIMAL(10, 2) DEFAULT 0
);

CREATE TABLE Orders (
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATETIME DEFAULT GETDATE(),
    TotalAmount DECIMAL(10, 2) CHECK (TotalAmount > 0),
    Status VARCHAR(20) DEFAULT 'Pending'
);

CREATE TABLE ErrorLog (
    ErrorID INT IDENTITY PRIMARY KEY,
    ErrorTime DATETIME DEFAULT GETDATE(),
    UserName VARCHAR(100) DEFAULT SUSER_NAME(),
    ErrorNumber INT,
    ErrorMessage VARCHAR(MAX),
    ErrorProcedure VARCHAR(200),
    ErrorLine INT
);

-- Insert sample data
INSERT INTO Customers (CustomerName, Email, Balance) VALUES
('John Doe', 'john@email.com', 1000.00),
('Jane Smith', 'jane@email.com', 500.00);

3. Basic TRY...CATCH with Error Functions

sql

BEGIN TRY
    -- Generate a constraint violation error
    INSERT INTO Orders (CustomerID, TotalAmount) 
    VALUES (999, 100.00); -- Invalid CustomerID
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 CATCH

4. Transaction Management with TRY...CATCH

Basic Transaction Rollback Pattern

sql

BEGIN TRY
    BEGIN TRANSACTION;
    
    -- Multiple operations
    UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 1;
    INSERT INTO Orders (CustomerID, TotalAmount) VALUES (1, 100.00);
    
    COMMIT TRANSACTION;
    PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    PRINT 'Transaction rolled back due to error.';
    
    -- Log error details
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;
END CATCH

5. Comprehensive Error Handling with Logging

sql

CREATE PROCEDURE usp_PlaceOrder
    @CustomerID INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check customer existence
        IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID)
            RAISERROR('Customer does not exist.', 16, 1);
        
        -- Check sufficient balance
        DECLARE @CurrentBalance DECIMAL(10, 2);
        SELECT @CurrentBalance = Balance FROM Customers WHERE CustomerID = @CustomerID;
        
        IF @CurrentBalance < @Amount
            RAISERROR('Insufficient balance.', 16, 1);
        
        -- Update balance
        UPDATE Customers SET Balance = Balance - @Amount WHERE CustomerID = @CustomerID;
        
        -- Create order
        INSERT INTO Orders (CustomerID, TotalAmount) 
        VALUES (@CustomerID, @Amount);
        
        COMMIT TRANSACTION;
        
        PRINT 'Order placed successfully.';
        RETURN 0; -- Success
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log error
        INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine)
        VALUES (
            ERROR_NUMBER(),
            ERROR_MESSAGE(),
            ERROR_PROCEDURE(),
            ERROR_LINE()
        );
        
        PRINT 'Error occurred: ' + ERROR_MESSAGE();
        RETURN -1; -- Error
    END CATCH
END

6. Testing the Error Handling

sql

-- Test 1: Successful order
DECLARE @Result INT;
EXEC @Result = usp_PlaceOrder @CustomerID = 1, @Amount = 100.00;
PRINT 'Return code: ' + CAST(@Result AS VARCHAR);

-- Test 2: Non-existent customer
EXEC @Result = usp_PlaceOrder @CustomerID = 999, @Amount = 100.00;
PRINT 'Return code: ' + CAST(@Result AS VARCHAR);

-- Test 3: Insufficient balance
EXEC @Result = usp_PlaceOrder @CustomerID = 2, @Amount = 1000.00;
PRINT 'Return code: ' + CAST(@Result AS VARCHAR);

-- Check error log
SELECT * FROM ErrorLog;

7. Advanced Error Handling Techniques

Re-throwing Errors

sql

CREATE PROCEDURE usp_AdvancedOrderProcessing
    @CustomerID INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Complex business logic
        EXEC usp_PlaceOrder @CustomerID, @Amount;
        
        -- Additional processing
        PRINT 'Additional processing completed.';
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log and re-throw
        INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE());
        
        -- Re-throw the original error
        THROW;
    END CATCH
END

Handling Specific Error Numbers

sql

CREATE PROCEDURE usp_SpecificErrorHandling
    @CustomerID INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        EXEC usp_PlaceOrder @CustomerID, @Amount;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber INT = ERROR_NUMBER();
        
        IF @ErrorNumber = 547 -- Constraint violation
        BEGIN
            PRINT 'Constraint violation occurred. Please check input values.';
            -- Custom handling for constraint errors
        END
        ELSE IF @ErrorNumber = 8134 -- Division by zero
        BEGIN
            PRINT 'Division by zero error detected.';
            -- Custom handling for math errors
        END
        ELSE
        BEGIN
            -- Re-throw unexpected errors
            THROW;
        END
    END CATCH
END

8. Nested TRY...CATCH Blocks

sql

CREATE PROCEDURE usp_NestedErrorHandling
AS
BEGIN
    BEGIN TRY
        PRINT 'Outer TRY block started.';
        
        BEGIN TRY
            PRINT 'Inner TRY block started.';
            SELECT 1/0; -- Generate error
            PRINT 'This will not execute.';
        END TRY
        BEGIN CATCH
            PRINT 'Inner CATCH block: ' + ERROR_MESSAGE();
            -- This error is handled, execution continues
        END CATCH
        
        PRINT 'Back in outer TRY block.';
        -- More code that might fail
        INSERT INTO NonExistentTable VALUES (1); -- Will cause error
        
    END TRY
    BEGIN CATCH
        PRINT 'Outer CATCH block: ' + ERROR_MESSAGE();
    END CATCH
END

EXEC usp_NestedErrorHandling;

9. Transaction Savepoints with Error Handling

sql

CREATE PROCEDURE usp_SavepointExample
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        SAVE TRANSACTION StartPoint;
        
        -- First operation
        UPDATE Customers SET Balance = Balance - 50 WHERE CustomerID = 1;
        
        SAVE TRANSACTION AfterFirstUpdate;
        
        -- Second operation that might fail
        INSERT INTO Orders (CustomerID, TotalAmount) VALUES (1, 50.00);
        
        COMMIT TRANSACTION;
        PRINT 'Transaction completed.';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            -- Roll back to savepoint instead of full transaction
            IF XACT_STATE() = 1 -- Transaction is active and committable
            BEGIN
                ROLLBACK TRANSACTION AfterFirstUpdate;
                PRINT 'Rolled back to after first update.';
                -- Continue with alternative logic
            END
            ELSE
            BEGIN
                ROLLBACK TRANSACTION;
                PRINT 'Full transaction rolled back.';
            END
        END
        
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
END

10. Best Practices for Error Handling

1. Always Check Transaction State

sql

BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE() = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END
    -- Handle error
END CATCH

2. Use Consistent Error Logging

sql

CREATE PROCEDURE usp_LogError
AS
BEGIN
    INSERT INTO ErrorLog (
        ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine, UserName
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_MESSAGE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        SUSER_NAME()
    );
    
    -- Return error log ID
    RETURN SCOPE_IDENTITY();
END

3. Implement Retry Logic for Transient Errors

sql

CREATE PROCEDURE usp_RetryOperation
    @MaxRetries INT = 3
AS
BEGIN
    DECLARE @RetryCount INT = 0;
    DECLARE @Success BIT = 0;
    
    WHILE @RetryCount < @MaxRetries AND @Success = 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            
            -- Your operation here
            PRINT 'Attempting operation...';
            
            COMMIT TRANSACTION;
            SET @Success = 1;
            PRINT 'Operation successful.';
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
            
            SET @RetryCount = @RetryCount + 1;
            
            IF @RetryCount >= @MaxRetries
            BEGIN
                PRINT 'Max retries exceeded. Error: ' + ERROR_MESSAGE();
                THROW;
            END
            
            WAITFOR DELAY '00:00:01'; -- Wait before retry
            PRINT 'Retry attempt: ' + CAST(@RetryCount AS VARCHAR);
        END CATCH
    END
END

11. Real-World Complete Example

sql

CREATE PROCEDURE usp_CompleteOrderProcessing
    @CustomerID INT,
    @OrderAmount DECIMAL(10, 2),
    @ShippingAddress VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ErrorCode INT = 0;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 1. Validate customer
        IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID)
            RAISERROR('Invalid customer ID: %d', 16, 1, @CustomerID);
        
        -- 2. Check balance
        DECLARE @CurrentBalance DECIMAL(10, 2);
        SELECT @CurrentBalance = Balance FROM Customers WHERE CustomerID = @CustomerID;
        
        IF @CurrentBalance < @OrderAmount
            RAISERROR('Insufficient balance. Current: %0.2f, Required: %0.2f', 
                    16, 1, @CurrentBalance, @OrderAmount);
        
        -- 3. Update balance
        UPDATE Customers SET Balance = Balance - @OrderAmount 
        WHERE CustomerID = @CustomerID;
        
        -- 4. Create order
        DECLARE @OrderID INT;
        INSERT INTO Orders (CustomerID, TotalAmount) 
        VALUES (@CustomerID, @OrderAmount);
        
        SET @OrderID = SCOPE_IDENTITY();
        
        -- 5. Additional processing (simulated)
        IF @OrderAmount > 500
        BEGIN
            -- Apply discount for large orders
            UPDATE Orders SET TotalAmount = TotalAmount * 0.9 
            WHERE OrderID = @OrderID;
        END
        
        COMMIT TRANSACTION;
        
        PRINT 'Order processed successfully. Order ID: ' + CAST(@OrderID AS VARCHAR);
        RETURN @OrderID;
    END TRY
    BEGIN CATCH
        SET @ErrorCode = ERROR_NUMBER();
        
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log error with custom procedure
        DECLARE @ErrorLogID INT;
        EXEC @ErrorLogID = usp_LogError;
        
        PRINT 'Error processed. Log ID: ' + CAST(@ErrorLogID AS VARCHAR);
        
        -- Re-throw for client application
        THROW;
    END CATCH
END

12. Testing and Validation

sql

-- Test various scenarios
DECLARE @Result INT;

-- Success case
EXEC @Result = usp_CompleteOrderProcessing 
    @CustomerID = 1, 
    @OrderAmount = 100.00, 
    @ShippingAddress = '123 Main St';
PRINT 'Result: ' + CAST(@Result AS VARCHAR);

-- Error cases
EXEC @Result = usp_CompleteOrderProcessing 
    @CustomerID = 999, 
    @OrderAmount = 100.00, 
    @ShippingAddress = '123 Main St';

EXEC @Result = usp_CompleteOrderProcessing 
    @CustomerID = 2, 
    @OrderAmount = 1000.00, 
    @ShippingAddress = '123 Main St';

-- Check error log
SELECT * FROM ErrorLog ORDER BY ErrorTime DESC;

Conclusion

Key Takeaways:

1. Always use TRY...CATCH for structured error handling

2. Manage transactions carefully - always check @@TRANCOUNT before rollback

3. Use error logging to track and diagnose issues

4. Implement specific error handling for different error types

5. Consider using savepoints for partial transaction rollbacks

6. Test error scenarios thoroughly

Best Practices:

• Use THROW instead of RAISERROR for re-throwing errors

• Always check transaction state before rollback

• Log errors with sufficient context for debugging

• Use consistent error handling patterns across your database

• Test both success and failure paths

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone