Home / SQL Server / T-SQL / T-SQL Error Handling and Real-World Scenarios: A Complete Guide for Beginners

T-SQL Error Handling and Real-World Scenarios: A Complete Guide for Beginners

On

Mastering T-SQL error handling and understanding real-world scenarios is essential for every SQL developer who wants to write robust, production-ready database code. Error handling in T-SQL allows you to gracefully manage exceptions, maintain data integrity, provide meaningful feedback, and ensure your applications continue functioning even when unexpected situations occur. In this comprehensive guide, I'll explain T-SQL error handling techniques and walk through realistic scenarios using the WideWorldImporters database, helping you develop the skills to handle errors professionally and build reliable database solutions.

Understanding error handling transforms you from someone who writes code that works "when everything goes right" to a developer who anticipates problems and builds resilient systems. These skills are crucial for anyone working with SQL Server in production environments.

Developer Insight: Early in my career, I wrote stored procedures that worked perfectly in testing but crashed in production. The difference? I hadn't accounted for errors—duplicate keys, null values, constraint violations. Learning proper error handling changed everything. Now I write defensive code that expects the unexpected.

Understanding T-SQL Error Handling with TRY...CATCH

The TRY...CATCH construct is the modern approach to error handling in SQL Server. It works like exception handling in programming languages—code that might cause errors goes in the TRY block, and error handling code goes in the CATCH block.

Basic TRY...CATCH Syntax

BEGIN TRY
    -- Code that might cause an error
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH;

When an error occurs in the TRY block, execution immediately transfers to the CATCH block. If no error occurs, the CATCH block is skipped entirely.

Simple Error Handling Example

-- Basic error handling demonstration
BEGIN TRY
    -- Attempt a division by zero (will cause error)
    DECLARE @Result INT;
    SET @Result = 100 / 0;
    
    PRINT 'This will not print';
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;

Error Information Functions

SQL Server provides several functions to retrieve error details within a CATCH block:

-- Comprehensive error information
BEGIN TRY
    -- Attempt to update non-existent record
    UPDATE Warehouse.StockItems
    SET UnitPrice = -10  -- Negative price may violate check constraint
    WHERE StockItemID = 999999;
    
    -- Force an error if no rows affected
    IF @@ROWCOUNT = 0
        THROW 50001, 'Stock item not found', 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 CATCH;

Error Functions Explained:

  • ERROR_NUMBER(): Numeric error code
  • ERROR_SEVERITY(): Error severity level (1-25)
  • ERROR_STATE(): Error state number
  • ERROR_PROCEDURE(): Name of stored procedure or trigger where error occurred
  • ERROR_LINE(): Line number where error occurred
  • ERROR_MESSAGE(): Complete error message text

Real-World Scenario 1: Safe Data Modification with Transactions

One of the most common real-world scenarios is safely modifying data with proper rollback on errors.

-- Update stock prices with error handling and transaction
CREATE OR ALTER PROCEDURE Warehouse.usp_UpdateStockPrices
    @SupplierID INT,
    @PercentIncrease DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate inputs
        IF @SupplierID IS NULL
            THROW 50001, 'Supplier ID cannot be NULL', 1;
            
        IF @PercentIncrease <= 0 OR @PercentIncrease > 100
            THROW 50002, 'Percent increase must be between 0 and 100', 1;
        
        -- Check supplier exists
        IF NOT EXISTS (SELECT 1 FROM Purchasing.Suppliers WHERE SupplierID = @SupplierID)
            THROW 50003, 'Supplier not found', 1;
        
        -- Perform the update
        UPDATE Warehouse.StockItems
        SET UnitPrice = UnitPrice * (1 + @PercentIncrease / 100.0),
            RecommendedRetailPrice = RecommendedRetailPrice * (1 + @PercentIncrease / 100.0),
            LastEditedWhen = SYSDATETIME()
        WHERE SupplierID = @SupplierID;
        
        DECLARE @RowsAffected INT = @@ROWCOUNT;
        
        COMMIT TRANSACTION;
        
        PRINT 'Success: Updated ' + CAST(@RowsAffected AS VARCHAR(10)) + ' stock items';
        
    END TRY
    BEGIN CATCH
        -- Rollback transaction if one is active
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log the error
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        
        PRINT 'Error: ' + @ErrorMsg;
        
        -- Re-throw the error to calling application
        THROW;
    END CATCH;
END;
GO

-- Test the procedure
EXEC Warehouse.usp_UpdateStockPrices @SupplierID = 4, @PercentIncrease = 5.0;

Developer Insight: The pattern above—validate inputs, start transaction, perform operations, commit on success, rollback on error—is my standard template for any data modification procedure. Copy this pattern and adapt it to your needs.

Real-World Scenario 2: Creating Orders with Multiple Related Records

Creating orders involves inserting into multiple related tables—a perfect scenario for transactions and error handling.

-- Create complete order with error handling
CREATE OR ALTER PROCEDURE Sales.usp_CreateCustomerOrder
    @CustomerID INT,
    @OrderDate DATE = NULL,
    @DeliveryDays INT = 7
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Ensures consistency with TRY...CATCH
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Set default order date
        IF @OrderDate IS NULL
            SET @OrderDate = CAST(GETDATE() AS DATE);
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            DECLARE @ErrorMsg NVARCHAR(100) = 'Customer ID ' + CAST(@CustomerID AS VARCHAR(10)) + ' not found';
            THROW 50001, @ErrorMsg, 1;
        END;
        
        -- Create the order
        DECLARE @OrderID INT;
        
        INSERT INTO Sales.Orders (
            CustomerID, 
            SalespersonPersonID,
            OrderDate, 
            ExpectedDeliveryDate,
            LastEditedBy,
            LastEditedWhen
        )
        VALUES (
            @CustomerID,
            2,  -- Default salesperson
            @OrderDate,
            DATEADD(DAY, @DeliveryDays, @OrderDate),
            1,  -- System user
            SYSDATETIME()
        );
        
        SET @OrderID = SCOPE_IDENTITY();
        
        -- Verify order was created
        IF @OrderID IS NULL
            THROW 50002, 'Failed to create order', 1;
        
        COMMIT TRANSACTION;
        
        -- Return success information
        SELECT 
            @OrderID AS OrderID,
            @CustomerID AS CustomerID,
            @OrderDate AS OrderDate,
            'Order created successfully' AS StatusMessage;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Provide detailed error information
        SELECT 
            'ERROR' AS Status,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_LINE() AS ErrorLine;
        
        -- Re-throw for application layer
        THROW;
    END CATCH;
END;
GO

-- Test the procedure
EXEC Sales.usp_CreateCustomerOrder @CustomerID = 1, @OrderDate = '2024-12-01';

Real-World Scenario 3: Inventory Management with Stock Level Validation

-- Adjust stock levels with business rule validation
CREATE OR ALTER PROCEDURE Warehouse.usp_AdjustStockLevel
    @StockItemID INT,
    @QuantityChange INT,
    @Reason NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Get current stock information
        DECLARE @CurrentQuantity INT;
        DECLARE @ReorderLevel INT;
        DECLARE @ItemName NVARCHAR(100);
        
        SELECT 
            @CurrentQuantity = sih.QuantityOnHand,
            @ReorderLevel = sih.ReorderLevel,
            @ItemName = si.StockItemName
        FROM Warehouse.StockItems si
        INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
        WHERE si.StockItemID = @StockItemID;
        
        -- Validate item exists
        IF @ItemName IS NULL
            THROW 50001, 'Stock item not found', 1;
        
        -- Calculate new quantity
        DECLARE @NewQuantity INT = @CurrentQuantity + @QuantityChange;
        
        -- Business rule: Cannot have negative inventory
        IF @NewQuantity < 0
        BEGIN
            DECLARE @ErrMsg NVARCHAR(200) = 
                'Insufficient stock for ' + @ItemName + 
                '. Current: ' + CAST(@CurrentQuantity AS VARCHAR(10)) + 
                ', Requested change: ' + CAST(@QuantityChange AS VARCHAR(10));
            THROW 50002, @ErrMsg, 1;
        END;
        
        -- Update stock level
        UPDATE Warehouse.StockItemHoldings
        SET QuantityOnHand = @NewQuantity,
            LastEditedWhen = SYSDATETIME()
        WHERE StockItemID = @StockItemID;
        
        -- Check if reorder needed
        DECLARE @Warning NVARCHAR(200) = '';
        IF @NewQuantity < @ReorderLevel
            SET @Warning = 'WARNING: Stock level below reorder point';
        
        COMMIT TRANSACTION;
        
        -- Return success with details
        SELECT 
            'SUCCESS' AS Status,
            @ItemName AS ItemName,
            @CurrentQuantity AS OldQuantity,
            @NewQuantity AS NewQuantity,
            @ReorderLevel AS ReorderLevel,
            @Warning AS WarningMessage;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        SELECT 
            'ERROR' AS Status,
            ERROR_MESSAGE() AS ErrorMessage;
        
        THROW;
    END CATCH;
END;
GO

-- Test successful adjustment
EXEC Warehouse.usp_AdjustStockLevel @StockItemID = 1, @QuantityChange = -5, @Reason = 'Sale';

-- Test error condition (too much reduction)
EXEC Warehouse.usp_AdjustStockLevel @StockItemID = 1, @QuantityChange = -10000, @Reason = 'Test';

Real-World Scenario 4: Error Logging System

Professional applications log errors for troubleshooting and auditing.

-- Create error log table (run once)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'ErrorLog' AND type = 'U')
BEGIN
    CREATE TABLE dbo.ErrorLog (
        ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,
        ErrorNumber INT,
        ErrorSeverity INT,
        ErrorState INT,
        ErrorProcedure NVARCHAR(200),
        ErrorLine INT,
        ErrorMessage NVARCHAR(4000),
        ErrorTime DATETIME2 DEFAULT SYSDATETIME(),
        UserName NVARCHAR(128) DEFAULT SYSTEM_USER,
        HostName NVARCHAR(128) DEFAULT HOST_NAME()
    );
END;
GO

-- Procedure with error logging
CREATE OR ALTER PROCEDURE Sales.usp_ProcessLargeOrder
    @CustomerID INT,
    @StockItemID INT,
    @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Business validation
        IF @Quantity > 1000
            THROW 50001, 'Order quantity exceeds maximum allowed (1000)', 1;
        
        -- Check stock availability
        DECLARE @AvailableStock INT;
        SELECT @AvailableStock = QuantityOnHand
        FROM Warehouse.StockItemHoldings
        WHERE StockItemID = @StockItemID;
        
        IF @AvailableStock < @Quantity
            THROW 50002, 'Insufficient stock available', 1;
        
        -- Create order (simplified)
        PRINT 'Order would be created here';
        
        -- Simulate random error for demonstration
        IF RAND() > 0.7
            THROW 50003, 'Random error for demonstration', 1;
        
        COMMIT TRANSACTION;
        PRINT 'Order processed successfully';
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log the error
        INSERT INTO dbo.ErrorLog (
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage
        )
        VALUES (
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
        );
        
        -- Display error to user
        PRINT 'An error occurred. Error has been logged.';
        PRINT 'Error: ' + ERROR_MESSAGE();
        
        -- Re-throw to application
        THROW;
    END CATCH;
END;
GO

-- Test and generate some errors
EXEC Sales.usp_ProcessLargeOrder @CustomerID = 1, @StockItemID = 1, @Quantity = 50;

-- View error log
SELECT 
    ErrorLogID,
    ErrorNumber,
    ErrorMessage,
    ErrorProcedure,
    ErrorLine,
    ErrorTime,
    UserName
FROM dbo.ErrorLog
ORDER BY ErrorTime DESC;

Developer Insight: Error logging saved my career more than once. When users report "something broke yesterday," I can query the error log to see exactly what happened, when, and why. Always log errors in production systems.

Real-World Scenario 5: Nested TRY...CATCH for Complex Operations

-- Complex operation with nested error handling
CREATE OR ALTER PROCEDURE Sales.usp_BatchProcessOrders
    @ProcessDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ProcessedCount INT = 0;
    DECLARE @ErrorCount INT = 0;
    
    BEGIN TRY
        PRINT 'Starting batch process for ' + CAST(@ProcessDate AS VARCHAR(10));
        
        -- Get orders to process
        DECLARE @OrderID INT;
        
        DECLARE order_cursor CURSOR FOR
        SELECT OrderID
        FROM Sales.Orders
        WHERE OrderDate = @ProcessDate
          AND OrderID <= 10;  -- Limit for demo
        
        OPEN order_cursor;
        FETCH NEXT FROM order_cursor INTO @OrderID;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Process each order in its own TRY...CATCH
            BEGIN TRY
                -- Simulate order processing
                PRINT 'Processing Order ID: ' + CAST(@OrderID AS VARCHAR(10));
                
                -- Simulate random failures
                IF @OrderID % 3 = 0
                    THROW 50001, 'Simulated processing error', 1;
                
                SET @ProcessedCount = @ProcessedCount + 1;
                
            END TRY
            BEGIN CATCH
                -- Log individual order error but continue processing
                SET @ErrorCount = @ErrorCount + 1;
                PRINT 'Error processing Order ' + CAST(@OrderID AS VARCHAR(10)) + ': ' + ERROR_MESSAGE();
            END CATCH;
            
            FETCH NEXT FROM order_cursor INTO @OrderID;
        END;
        
        CLOSE order_cursor;
        DEALLOCATE order_cursor;
        
        -- Summary
        PRINT '';
        PRINT 'Batch processing complete';
        PRINT 'Successfully processed: ' + CAST(@ProcessedCount AS VARCHAR(10));
        PRINT 'Errors encountered: ' + CAST(@ErrorCount AS VARCHAR(10));
        
    END TRY
    BEGIN CATCH
        -- Handle catastrophic errors
        IF CURSOR_STATUS('global', 'order_cursor') >= 0
        BEGIN
            CLOSE order_cursor;
            DEALLOCATE order_cursor;
        END;
        
        PRINT 'CRITICAL ERROR: Batch process failed';
        PRINT ERROR_MESSAGE();
        
        THROW;
    END CATCH;
END;
GO

-- Test batch processing
EXEC Sales.usp_BatchProcessOrders @ProcessDate = '2016-01-01';

Common Mistakes to Avoid

Mistake 1: Not Rolling Back Transactions on Error

Problem: Leaving transactions open causes locking and blocks other users.

-- WRONG: Transaction not rolled back
BEGIN TRANSACTION;
UPDATE Warehouse.StockItems SET UnitPrice = -10 WHERE StockItemID = 1;
-- Error occurs, transaction stays open!

-- CORRECT: Always rollback on error
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Warehouse.StockItems SET UnitPrice = -10 WHERE StockItemID = 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Mistake 2: Swallowing Errors Without Logging

Problem: Errors disappear, making debugging impossible.

-- WRONG: Error silently ignored
BEGIN TRY
    -- Risky operation
    UPDATE Warehouse.StockItems SET UnitPrice = UnitPrice * 1.1;
END TRY
BEGIN CATCH
    PRINT 'Error occurred';  -- No details!
END CATCH;

-- CORRECT: Log details and re-throw
BEGIN TRY
    UPDATE Warehouse.StockItems SET UnitPrice = UnitPrice * 1.1;
END TRY
BEGIN CATCH
    INSERT INTO dbo.ErrorLog (...) VALUES (...);
    THROW;  -- Re-throw to caller
END CATCH;

Mistake 3: Not Using SET XACT_ABORT ON

Problem: Some errors don't trigger CATCH blocks as expected.

-- BEST PRACTICE: Always set at procedure start
CREATE PROCEDURE MyProcedure
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Critical for consistent error handling
    
    BEGIN TRY
        -- Your code here
    END TRY
    BEGIN CATCH
        -- Error handling
    END CATCH;
END;

Mistake 4: Not Validating Inputs

Problem: Garbage in, garbage out—and often, crashes.

-- WRONG: No validation
CREATE PROCEDURE BadProcedure @CustomerID INT
AS
BEGIN
    -- Directly use parameter
    SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID;
END;

-- CORRECT: Validate inputs
CREATE PROCEDURE GoodProcedure @CustomerID INT
AS
BEGIN
    BEGIN TRY
        IF @CustomerID IS NULL
            THROW 50001, 'CustomerID cannot be NULL', 1;
            
        IF @CustomerID <= 0
            THROW 50002, 'CustomerID must be positive', 1;
            
        IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
            THROW 50003, 'Customer not found', 1;
        
        SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END;

Mistake 5: Using RAISERROR Instead of THROW

Problem: RAISERROR doesn't automatically abort execution.

-- OLD WAY: RAISERROR (less reliable)
IF @Value < 0
    RAISERROR('Value must be positive', 16, 1);
-- Execution continues!

-- MODERN WAY: THROW (recommended)
IF @Value < 0
    THROW 50001, 'Value must be positive', 1;
-- Execution stops immediately

Best Practices for Error Handling

1. Always use transactions with error handling

BEGIN TRY
    BEGIN TRANSACTION;
    -- operations
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

2. Set XACT_ABORT ON at the start of stored procedures

3. Log errors to a dedicated error table

4. Validate inputs before processing

5. Use THROW instead of RAISERROR for new code

6. Clean up resources (cursors, temp tables) in CATCH blocks

7. Provide meaningful error messages that help troubleshooting

8. Test error handling with intentional failures

Summary and Key Takeaways

Mastering T-SQL error handling is essential for professional database development. Here's what you need to remember:

TRY...CATCH Structure:

  • TRY block contains code that might fail
  • CATCH block handles errors
  • Use error functions (ERROR_NUMBER, ERROR_MESSAGE, etc.) for details

Transaction Management:

  • Always check @@TRANCOUNT before ROLLBACK
  • Use SET XACT_ABORT ON for consistency
  • Commit on success, rollback on error

Error Information:

  • ERROR_NUMBER(): Error code
  • ERROR_MESSAGE(): Error description
  • ERROR_LINE(): Where error occurred
  • ERROR_PROCEDURE(): Which procedure failed

Real-World Patterns:

  • Input validation at procedure start
  • Transactions wrapping related operations
  • Error logging for troubleshooting
  • Meaningful error messages for users
  • Resource cleanup in CATCH blocks

Developer Insight: The examples in this guide represent patterns I use daily in production code. The WideWorldImporters database provides realistic scenarios for practice. Start with simple procedures, add error handling, test deliberately induced failures, and gradually increase complexity. Professional error handling separates junior from senior developers—it's not optional, it's essential.

Next Steps: Practice by adding error handling to existing stored procedures. Create an error log table and implement logging. Test failure scenarios deliberately. Build a library of reusable error handling patterns. The confidence to handle errors comes from experiencing them in controlled environments.

Frequently Asked Questions

Q: Should I always re-throw errors with THROW after catching them?

A: It depends on your architecture. If your stored procedure is called by application code that needs to know about the error, use THROW to pass it up. If you're logging the error and the caller doesn't need details, you might return an error code instead. For most business logic procedures, I recommend: log the error locally, then THROW to inform the application something went wrong. The application can then decide how to present it to users.

Q: What's the difference between THROW and RAISERROR?

A: THROW is the modern, simpler approach introduced in SQL Server 2012. THROW automatically stops execution and requires less syntax. RAISERROR is older, has more options (like logging to Windows event log), but doesn't automatically abort execution—you must also use RETURN afterward. For new code, use THROW. It's cleaner: THROW 50001, 'Error message', 1; versus RAISERROR('Error message', 16, 1); RETURN;

Q: Do I need error handling if my application has error handling?

A: Yes! Database-level error handling provides several benefits: (1) maintains data integrity through proper transaction management, (2) logs errors at the source for better troubleshooting, (3) provides specific business rule validation, and (4) makes stored procedures reusable across different applications. Think of it as defense in depth—both layers protect your data and provide different perspectives on problems. Application code might fail to connect, but database errors tell you what SQL operations failed and why.

Tags: