Stored Procedures - Learning Module

Loading content...

0/241

Error Handling

When Things Go Wrong

In production systems, errors aren't exceptions to the rule—they're a guarantee. Networks fail, constraints are violated, deadlocks occur, data is missing, and business rules are breached. Stored procedures without proper error handling are ticking time bombs: they may leave transactions half-committed, corrupt data integrity, crash silently, or provide no diagnostic information when failures occur.

Robust error handling transforms procedures from fragile scripts into production-grade operations that fail gracefully, preserve data integrity, provide meaningful diagnostics, and enable automated recovery or escalation.

What You Will Learn

By the end of this page, you will master TRY/CATCH blocks, exception handlers across database systems, transaction safety during errors, error logging patterns, custom error raising, error propagation strategies, and professional patterns for building procedures that handle failure gracefully.

Error Handling Fundamentals

Before diving into syntax, understand what happens when errors occur in stored procedures:

Without error handling:

Statement that caused the error stops
Depending on error severity, subsequent statements may or may not execute
Transaction state is unpredictable (may be partially committed)
Calling application receives cryptic database error
No logging, no context, no graceful degradation

With proper error handling:

Error is detected and control transfers to handler
Transaction is explicitly rolled back (data remains consistent)
Error is logged with context (who, what, when, why)
Meaningful error is returned to caller
Procedure exits cleanly in known state

Error Handling Mechanisms by Database
Database	Primary Mechanism	Error Info Functions	Raise Custom Error
SQL Server	TRY...CATCH	ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE()	RAISERROR, THROW
PostgreSQL	EXCEPTION block	SQLSTATE, SQLERRM	RAISE EXCEPTION
MySQL	DECLARE HANDLER	SQLSTATE, MYSQL_ERRNO	SIGNAL SQLSTATE
Oracle	EXCEPTION block	SQLCODE, SQLERRM	RAISE_APPLICATION_ERROR

Error severity levels (SQL Server example):

Severity	Category	Examples	Behavior
0-10	Informational	Warnings, status messages	Not caught by CATCH
11-16	User errors	Constraint violations, syntax	Caught by CATCH
17-19	Software/resource	Out of memory, lock timeout	Caught by CATCH
20-25	Fatal	Database corruption, session terminated	Connection broken

TRY/CATCH Blocks

TRY/CATCH is the primary error handling mechanism in SQL Server and conceptually similar constructs exist in PostgreSQL and Oracle. Code that might fail goes in TRY; error handling logic goes in CATCH.

Structure:

BEGIN TRY
    -- Code that might fail
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

try_catch.sql

-- Comprehensive TRY/CATCH pattern
CREATE PROCEDURE Sales.ProcessPayment
    @OrderID INT,
    @PaymentAmount DECIMAL(18,2),
    @PaymentMethod VARCHAR(50),
    @TransactionID UNIQUEIDENTIFIER OUTPUT,
    @Success BIT OUTPUT,
    @ErrorCode VARCHAR(20) OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Initialize outputs
    SET @TransactionID = NULL;
    SET @Success = 0;
    SET @ErrorCode = NULL;
    SET @ErrorMessage = NULL;
    
    -- Pre-validation (before transaction)
    IF @OrderID IS NULL OR @PaymentAmount <= 0
    BEGIN
        SET @ErrorCode = 'INVALID_INPUT';
        SET @ErrorMessage = 'OrderID and positive PaymentAmount are required';
        RETURN;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check order exists and get amount due
        DECLARE @AmountDue DECIMAL(18,2);
        DECLARE @OrderStatus VARCHAR(20);
        
        SELECT @AmountDue = AmountDue, @OrderStatus = Status
        FROM Sales.Orders WITH (UPDLOCK)  -- Lock row during transaction
        WHERE OrderID = @OrderID;
        
        IF @AmountDue IS NULL
        BEGIN
            -- This is a business error, not a DB error
            -- But we want to handle it in CATCH for consistency
            RAISERROR('Order not found', 16, 1);
        END
        
        IF @OrderStatus = 'Cancelled'
        BEGIN
            RAISERROR('Cannot pay for cancelled order', 16, 1);
        END
        
        IF @PaymentAmount > @AmountDue
        BEGIN
            RAISERROR('Payment exceeds amount due', 16, 1);
        END
        
        -- Generate transaction ID
        SET @TransactionID = NEWID();
        
        -- Record payment
        INSERT INTO Sales.Payments (
            TransactionID, OrderID, Amount, PaymentMethod, 
            PaymentDate, Status
        )
        VALUES (
            @TransactionID, @OrderID, @PaymentAmount, @PaymentMethod,
            GETDATE(), 'Completed'
        );
        
        -- Update order
        UPDATE Sales.Orders
        SET AmountDue = AmountDue - @PaymentAmount,
            AmountPaid = ISNULL(AmountPaid, 0) + @PaymentAmount,
            Status = CASE 
                WHEN AmountDue - @PaymentAmount = 0 THEN 'Paid'
                ELSE Status 
            END,
            LastPaymentDate = GETDATE()
        WHERE OrderID = @OrderID;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        -- Always check for open transaction first!
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Capture error information
        SET @ErrorCode = 'DB_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
        
        -- Log error with full context
        INSERT INTO Logs.Errors (
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage,
            ErrorDate,
            UserName,
            AdditionalInfo
        )
        VALUES (
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            GETDATE(),
            SUSER_SNAME(),
            'OrderID=' + CAST(@OrderID AS VARCHAR) + 
            ', Amount=' + CAST(@PaymentAmount AS VARCHAR)
        );
        
        -- Set success = 0 (already initialized, but explicit)
        SET @Success = 0;
        
    END CATCH
END;
GO
 
-- Error information functions in SQL Server
/*
ERROR_NUMBER()    - Unique error number
ERROR_SEVERITY()  - Severity level (0-25)
ERROR_STATE()     - State number (useful for Microsoft support)
ERROR_PROCEDURE() - Name of procedure where error occurred
ERROR_LINE()      - Line number in procedure
ERROR_MESSAGE()   - Full error message text
*/

Transaction Safety During Errors

Error handling and transactions must work together. The most critical rule: when an error occurs, ensure the transaction is properly handled to maintain data consistency.

Key principles:

Always check for open transactions in CATCH — Use @@TRANCOUNT (SQL Server) or equivalent
Rollback before exiting — Don't leave transactions dangling
Consider savepoints — For partial rollback within nested operations
Beware of implicit commits — Some errors cause automatic rollback

transaction_safety.sql

-- Transaction-safe error handling
CREATE PROCEDURE Orders.ComplexOrderProcess
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Automatically rollback on severe errors
    
    DECLARE @TranStarted BIT = 0;
    
    BEGIN TRY
        -- Only start transaction if not already in one
        IF @@TRANCOUNT = 0
        BEGIN
            BEGIN TRANSACTION;
            SET @TranStarted = 1;
        END
        
        -- Phase 1: Validate order
        IF NOT EXISTS (SELECT 1 FROM Orders.Orders WHERE OrderID = @OrderID)
            RAISERROR('Order not found', 16, 1);
        
        -- Phase 2: Reserve inventory
        -- Use savepoint for partial rollback capability
        SAVE TRANSACTION InventoryReservation;
        
        UPDATE Inventory.Stock
        SET ReservedQty = ReservedQty + ol.Quantity
        FROM Inventory.Stock s
        JOIN Orders.OrderLines ol ON s.ProductID = ol.ProductID
        WHERE ol.OrderID = @OrderID;
        
        -- Phase 3: Process payment (might fail)
        BEGIN TRY
            EXEC Payments.ProcessPayment @OrderID = @OrderID;
        END TRY
        BEGIN CATCH
            -- Payment failed - rollback just the inventory reservation
            ROLLBACK TRANSACTION InventoryReservation;
            
            -- Re-throw the error to outer catch
            THROW;
        END CATCH
        
        -- Phase 4: Update order status
        UPDATE Orders.Orders
        SET Status = 'Confirmed', ConfirmedDate = GETDATE()
        WHERE OrderID = @OrderID;
        
        -- Commit only if we started the transaction
        IF @TranStarted = 1
            COMMIT TRANSACTION;
        
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        -- Handle transaction state
        IF @TranStarted = 1 AND @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
        
        -- Log the error
        EXEC Logs.LogError 
            @ProcName = 'ComplexOrderProcess',
            @ErrorMsg = @ErrorMessage,
            @OrderID = @OrderID;
    END CATCH
END;
GO
 
-- A common pattern: Transaction wrapper procedure
CREATE PROCEDURE Utils.ExecuteWithTransaction
    @SQLToExecute NVARCHAR(MAX),
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        EXEC sp_executesql @SQLToExecute;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
            
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

The @@TRANCOUNT Trap

In SQL Server, @@TRANCOUNT tracks nested transaction depth. A BEGIN TRANSACTION increments it; COMMIT decrements it; ROLLBACK resets it to 0. Always check @@TRANCOUNT > 0 before ROLLBACK to avoid errors when no transaction is active.

Raising Custom Errors

Custom errors communicate business rule violations, validation failures, and other application-level issues. They transform silent failures into actionable information.

When to raise custom errors:

Business rule violations ("Cannot process order older than 30 days")
Validation failures ("Invalid discount percentage")
Missing prerequisites ("Customer credit limit not set")
State violations ("Order already shipped")

raise_errors.sql

-- RAISERROR (legacy but widely used)
-- Syntax: RAISERROR (message, severity, state)
-- Severity 16 = user error, caught by TRY/CATCH
-- State 1 = user-defined state (1 is common default)
 
CREATE PROCEDURE Validation.ValidateCustomer
    @CustomerID INT
AS
BEGIN
    DECLARE @CreditLimit DECIMAL(18,2);
    DECLARE @AccountStatus VARCHAR(20);
    
    SELECT @CreditLimit = CreditLimit, @AccountStatus = AccountStatus
    FROM Customers
    WHERE CustomerID = @CustomerID;
    
    -- Not found error
    IF @CreditLimit IS NULL
    BEGIN
        RAISERROR('Customer %d does not exist', 16, 1, @CustomerID);
        RETURN;
    END
    
    -- Business rule errors
    IF @AccountStatus = 'Suspended'
    BEGIN
        RAISERROR('Customer %d account is suspended', 16, 1, @CustomerID);
        RETURN;
    END
    
    IF @CreditLimit <= 0
    BEGIN
        RAISERROR('Customer %d has no credit limit established', 16, 1, @CustomerID);
        RETURN;
    END
END;
GO
 
-- THROW (SQL Server 2012+, preferred)
-- Syntax: THROW error_number, message, state
-- Or just THROW (re-throws current error in CATCH)
 
CREATE PROCEDURE Orders.ValidateOrder
    @OrderID INT,
    @CustomerID INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Validation errors with THROW
    IF @OrderID IS NULL
        THROW 50001, 'OrderID is required', 1;
    
    IF @Amount <= 0
        THROW 50002, 'Order amount must be positive', 1;
    
    -- Check customer exists and has credit
    DECLARE @AvailableCredit DECIMAL(18,2);
    
    SELECT @AvailableCredit = CreditLimit - ISNULL(OutstandingBalance, 0)
    FROM Customers
    WHERE CustomerID = @CustomerID;
    
    IF @AvailableCredit IS NULL
        THROW 50003, 'Customer not found', 1;
    
    IF @Amount > @AvailableCredit
    BEGIN
        DECLARE @ErrorMsg NVARCHAR(500) = 
            CONCAT('Insufficient credit. Required: $', @Amount, 
                   ', Available: $', @AvailableCredit);
        THROW 50004, @ErrorMsg, 1;
    END
END;
GO
 
-- Custom error numbers (50000+ for user-defined)
-- Best practice: Define and document error number ranges
/*
50001-50099: Input validation errors
50100-50199: Business rule errors  
50200-50299: Data integrity errors
50300-50399: External system errors
*/
 
-- Re-throwing errors (preserves original error info)
CREATE PROCEDURE Wrapper.ExecuteWithLogging
    @ProcedureName NVARCHAR(200)
AS
BEGIN
    BEGIN TRY
        EXEC sp_executesql @ProcedureName;
    END TRY
    BEGIN CATCH
        -- Log the error...
        INSERT INTO Logs.Errors (ErrorMessage, ErrorNumber, ProcedureName, ErrorDate)
        VALUES (ERROR_MESSAGE(), ERROR_NUMBER(), @ProcedureName, GETDATE());
        
        -- Re-throw to caller (preserves original error details)
        THROW;  -- No parameters = re-throw current error
    END CATCH
END;

Error Logging Patterns

Production procedures must log errors for debugging and audit purposes. A well-designed error log captures:

What went wrong (error message, code)
Where it happened (procedure, line number)
When it occurred (timestamp)
Who was affected (user, session)
Context (input parameters, state information)

error_logging.sql

SQL Server

-- Error log table
CREATE TABLE Logs.Errors (
    ErrorLogID BIGINT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(200),
    ErrorLine INT,
    ErrorMessage NVARCHAR(4000),
    ErrorDate DATETIME2 DEFAULT SYSDATETIME(),
    UserName NVARCHAR(200) DEFAULT SUSER_SNAME(),
    HostName NVARCHAR(200) DEFAULT HOST_NAME(),
    ApplicationName NVARCHAR(200) DEFAULT APP_NAME(),
    AdditionalContext NVARCHAR(MAX),
    INDEX IX_ErrorDate (ErrorDate)
);
GO
 
-- Reusable error logging procedure
CREATE PROCEDURE Logs.LogError
    @AdditionalContext NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO Logs.Errors (
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine,
        ErrorMessage,
        AdditionalContext
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        @AdditionalContext
    );
    
    -- Return the log ID for reference
    SELECT SCOPE_IDENTITY() AS ErrorLogID;
END;
GO
 
-- Using the logging procedure in a CATCH block
CREATE PROCEDURE Orders.CreateOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT,
    @OrderID INT OUTPUT,
    @ErrorLogID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SET @OrderID = NULL;
    SET @ErrorLogID = NULL;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Create order logic...
        INSERT INTO Orders.Orders (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, GETDATE(), 'New');
        
        SET @OrderID = SCOPE_IDENTITY();
        
        INSERT INTO Orders.OrderLines (OrderID, ProductID, Quantity)
        VALUES (@OrderID, @ProductID, @Quantity);
        
        -- Intentional error for demo (divide by zero)
        DECLARE @Test INT = 1 / 0;
        
        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Build context string
        DECLARE @Context NVARCHAR(MAX) = CONCAT(
            'CustomerID=', @CustomerID, 
            ', ProductID=', @ProductID,
            ', Quantity=', @Quantity,
            ', Attempted OrderID=', @OrderID
        );
        
        -- Log error and capture log ID
        EXEC Logs.LogError @AdditionalContext = @Context;
        SELECT @ErrorLogID = SCOPE_IDENTITY();
        
        SET @OrderID = NULL;
    END CATCH
END;
GO
 
-- Structured error context with JSON
CREATE PROCEDURE Logs.LogErrorWithJson
    @ContextJson NVARCHAR(MAX) = NULL
AS
BEGIN
    INSERT INTO Logs.Errors (
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine,
        ErrorMessage,
        AdditionalContext
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        @ContextJson
    );
END;
GO
 
-- Usage with JSON context
BEGIN TRY
    -- ... code that fails ...
END TRY
BEGIN CATCH
    DECLARE @JsonContext NVARCHAR(MAX) = (
        SELECT 
            @OrderID AS order_id,
            @CustomerID AS customer_id,
            @Amount AS amount,
            GETDATE() AS error_timestamp
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    
    EXEC Logs.LogErrorWithJson @ContextJson = @JsonContext;
END CATCH

Error Log Retention

Error logs grow quickly. Implement automated archival or purging of old error logs. Consider partitioning by date for efficient maintenance. Index ErrorDate for quick diagnostics of recent issues.

Professional Error Handling Patterns

Production-ready procedures use consistent, standardized error handling patterns. Here are proven patterns used in enterprise systems:

Standard Error Handling Patterns

•Result Pattern — Return success flag, error code, and error message as OUT parameters. Caller checks flag before using results.
•Exception Bubbling — Catch, log, and re-throw. Preserves original error while adding logging.
•Error Transformation — Convert database errors to application-friendly messages while logging technical details.
•Retry Pattern — For transient errors (deadlocks, timeouts), attempt retry with exponential backoff.
•Circuit Breaker — Track failure rates and temporarily disable operations that consistently fail.

error_patterns.sql

SQL Server

-- Pattern 1: Standardized Result Pattern
CREATE TYPE dbo.OperationResult AS TABLE (
    Success BIT,
    ErrorCode VARCHAR(50),
    ErrorMessage NVARCHAR(500),
    ResultData NVARCHAR(MAX)  -- JSON for flexible data
);
GO
 
CREATE PROCEDURE Sales.CreateOrderWithResult
    @CustomerID INT,
    @Items NVARCHAR(MAX),  -- JSON array of items
    @Result OperationResult READONLY OUTPUT  -- Would use table variable in practice
    -- Note: TVPs can't be OUTPUT, so we use this pattern differently:
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Success BIT = 0;
    DECLARE @ErrorCode VARCHAR(50) = NULL;
    DECLARE @ErrorMessage NVARCHAR(500) = NULL;
    DECLARE @OrderID INT = NULL;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validation
        IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID)
        BEGIN
            SET @ErrorCode = 'CUSTOMER_NOT_FOUND';
            SET @ErrorMessage = 'Customer does not exist';
            GOTO ReturnResult;
        END
        
        -- Create order
        INSERT INTO Sales.Orders (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, GETDATE(), 'New');
        SET @OrderID = SCOPE_IDENTITY();
        
        -- Insert items from JSON
        INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
        SELECT @OrderID, ProductID, Quantity, UnitPrice
        FROM OPENJSON(@Items)
        WITH (
            ProductID INT,
            Quantity INT,
            UnitPrice DECIMAL(18,2)
        );
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        
        SET @ErrorCode = 'SYSTEM_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
        
        EXEC Logs.LogError @AdditionalContext = @Items;
    END CATCH
    
    ReturnResult:
    -- Return standardized result
    SELECT 
        @Success AS Success,
        @ErrorCode AS ErrorCode,
        @ErrorMessage AS ErrorMessage,
        CASE WHEN @Success = 1 
            THEN JSON_OBJECT('OrderID': @OrderID)
            ELSE NULL 
        END AS ResultData;
END;
GO
 
-- Pattern 2: Retry with Exponential Backoff
CREATE PROCEDURE Utils.ExecuteWithRetry
    @SQL NVARCHAR(MAX),
    @MaxRetries INT = 3,
    @DelayMs INT = 100,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @RetryCount INT = 0;
    DECLARE @CurrentDelay INT = @DelayMs;
    DECLARE @ErrorMessage NVARCHAR(500);
    
    SET @Success = 0;
    
    WHILE @RetryCount < @MaxRetries AND @Success = 0
    BEGIN
        BEGIN TRY
            EXEC sp_executesql @SQL;
            SET @Success = 1;  -- Success!
        END TRY
        BEGIN CATCH
            SET @RetryCount = @RetryCount + 1;
            SET @ErrorMessage = ERROR_MESSAGE();
            
            -- Only retry on transient errors
            IF ERROR_NUMBER() IN (
                1205,   -- Deadlock
                -2,     -- Timeout
                1222,   -- Lock timeout
                8645,   -- Timeout waiting for memory
                8651    -- Low memory condition
            )
            BEGIN
                IF @RetryCount < @MaxRetries
                BEGIN
                    -- Wait with exponential backoff
                    DECLARE @DelayStr VARCHAR(12) = 
                        '00:00:0' + CAST(@CurrentDelay / 1000.0 AS VARCHAR);
                    WAITFOR DELAY @DelayStr;
                    SET @CurrentDelay = @CurrentDelay * 2;  -- Double for next retry
                END
            END
            ELSE
            BEGIN
                -- Non-transient error, don't retry
                SET @RetryCount = @MaxRetries;  -- Exit loop
            END
        END CATCH
    END
    
    IF @Success = 0
    BEGIN
        -- All retries failed
        EXEC Logs.LogError @AdditionalContext = @SQL;
    END
END;

Summary: Robust Error Handling

We've covered comprehensive error handling for stored procedures. Here are the essential takeaways:

Key Takeaways

•TRY/CATCH blocks are essential — Wrap potentially failing code to handle errors gracefully. Each database has its own syntax.
•Always check transaction state — Use @@TRANCOUNT (or equivalent) before ROLLBACK to avoid errors when no transaction is active.
•Roll back on error — Uncommitted changes after an error leave data in an inconsistent state. Always rollback explicitly.
•Use savepoints for partial rollback — When processing multiple operations, savepoints allow rolling back specific parts while preserving others.
•Raise custom errors — THROW, RAISERROR, RAISE EXCEPTION, or SIGNAL communicate business rule violations clearly.
•Log errors comprehensively — Capture what, where, when, who, and context for every error. Enable debugging without reproducing issues.
•Use consistent patterns — Standardize error handling across all procedures. Result patterns, retry patterns, and circuit breakers provide reliability.
•Test error paths — Error handling code needs testing too. Deliberately trigger errors to verify proper behavior.

Module Complete!

You now have a comprehensive understanding of stored procedures—from concept to creation, parameters, control flow, and error handling. You're equipped to write production-ready procedures that are efficient, maintainable, and robust.

Module Complete

Congratulations! You've completed the Stored Procedures module. You can now create, parameterize, control, and error-handle stored procedures at a professional level. Apply these patterns to build reliable database applications.

Error Handling

When Things Go Wrong

What You Will Learn

Error Handling Fundamentals

Before diving into syntax, understand what happens when errors occur in stored procedures:

Without error handling:

Statement that caused the error stops
Depending on error severity, subsequent statements may or may not execute
Transaction state is unpredictable (may be partially committed)
Calling application receives cryptic database error
No logging, no context, no graceful degradation

With proper error handling:

Error is detected and control transfers to handler
Transaction is explicitly rolled back (data remains consistent)
Error is logged with context (who, what, when, why)
Meaningful error is returned to caller
Procedure exits cleanly in known state

Error Handling Mechanisms by Database
Database	Primary Mechanism	Error Info Functions	Raise Custom Error
SQL Server	TRY...CATCH	ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE()	RAISERROR, THROW
PostgreSQL	EXCEPTION block	SQLSTATE, SQLERRM	RAISE EXCEPTION
MySQL	DECLARE HANDLER	SQLSTATE, MYSQL_ERRNO	SIGNAL SQLSTATE
Oracle	EXCEPTION block	SQLCODE, SQLERRM	RAISE_APPLICATION_ERROR

Error severity levels (SQL Server example):

Severity	Category	Examples	Behavior
0-10	Informational	Warnings, status messages	Not caught by CATCH
11-16	User errors	Constraint violations, syntax	Caught by CATCH
17-19	Software/resource	Out of memory, lock timeout	Caught by CATCH
20-25	Fatal	Database corruption, session terminated	Connection broken

TRY/CATCH Blocks

Structure:

BEGIN TRY
    -- Code that might fail
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

try_catch.sql

-- Comprehensive TRY/CATCH pattern
CREATE PROCEDURE Sales.ProcessPayment
    @OrderID INT,
    @PaymentAmount DECIMAL(18,2),
    @PaymentMethod VARCHAR(50),
    @TransactionID UNIQUEIDENTIFIER OUTPUT,
    @Success BIT OUTPUT,
    @ErrorCode VARCHAR(20) OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Initialize outputs
    SET @TransactionID = NULL;
    SET @Success = 0;
    SET @ErrorCode = NULL;
    SET @ErrorMessage = NULL;
    
    -- Pre-validation (before transaction)
    IF @OrderID IS NULL OR @PaymentAmount <= 0
    BEGIN
        SET @ErrorCode = 'INVALID_INPUT';
        SET @ErrorMessage = 'OrderID and positive PaymentAmount are required';
        RETURN;
    END
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check order exists and get amount due
        DECLARE @AmountDue DECIMAL(18,2);
        DECLARE @OrderStatus VARCHAR(20);
        
        SELECT @AmountDue = AmountDue, @OrderStatus = Status
        FROM Sales.Orders WITH (UPDLOCK)  -- Lock row during transaction
        WHERE OrderID = @OrderID;
        
        IF @AmountDue IS NULL
        BEGIN
            -- This is a business error, not a DB error
            -- But we want to handle it in CATCH for consistency
            RAISERROR('Order not found', 16, 1);
        END
        
        IF @OrderStatus = 'Cancelled'
        BEGIN
            RAISERROR('Cannot pay for cancelled order', 16, 1);
        END
        
        IF @PaymentAmount > @AmountDue
        BEGIN
            RAISERROR('Payment exceeds amount due', 16, 1);
        END
        
        -- Generate transaction ID
        SET @TransactionID = NEWID();
        
        -- Record payment
        INSERT INTO Sales.Payments (
            TransactionID, OrderID, Amount, PaymentMethod, 
            PaymentDate, Status
        )
        VALUES (
            @TransactionID, @OrderID, @PaymentAmount, @PaymentMethod,
            GETDATE(), 'Completed'
        );
        
        -- Update order
        UPDATE Sales.Orders
        SET AmountDue = AmountDue - @PaymentAmount,
            AmountPaid = ISNULL(AmountPaid, 0) + @PaymentAmount,
            Status = CASE 
                WHEN AmountDue - @PaymentAmount = 0 THEN 'Paid'
                ELSE Status 
            END,
            LastPaymentDate = GETDATE()
        WHERE OrderID = @OrderID;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        -- Always check for open transaction first!
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Capture error information
        SET @ErrorCode = 'DB_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
        
        -- Log error with full context
        INSERT INTO Logs.Errors (
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage,
            ErrorDate,
            UserName,
            AdditionalInfo
        )
        VALUES (
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            GETDATE(),
            SUSER_SNAME(),
            'OrderID=' + CAST(@OrderID AS VARCHAR) + 
            ', Amount=' + CAST(@PaymentAmount AS VARCHAR)
        );
        
        -- Set success = 0 (already initialized, but explicit)
        SET @Success = 0;
        
    END CATCH
END;
GO
 
-- Error information functions in SQL Server
/*
ERROR_NUMBER()    - Unique error number
ERROR_SEVERITY()  - Severity level (0-25)
ERROR_STATE()     - State number (useful for Microsoft support)
ERROR_PROCEDURE() - Name of procedure where error occurred
ERROR_LINE()      - Line number in procedure
ERROR_MESSAGE()   - Full error message text
*/

Transaction Safety During Errors

Error handling and transactions must work together. The most critical rule: when an error occurs, ensure the transaction is properly handled to maintain data consistency.

Key principles:

Always check for open transactions in CATCH — Use @@TRANCOUNT (SQL Server) or equivalent
Rollback before exiting — Don't leave transactions dangling
Consider savepoints — For partial rollback within nested operations
Beware of implicit commits — Some errors cause automatic rollback

transaction_safety.sql

-- Transaction-safe error handling
CREATE PROCEDURE Orders.ComplexOrderProcess
    @OrderID INT,
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Automatically rollback on severe errors
    
    DECLARE @TranStarted BIT = 0;
    
    BEGIN TRY
        -- Only start transaction if not already in one
        IF @@TRANCOUNT = 0
        BEGIN
            BEGIN TRANSACTION;
            SET @TranStarted = 1;
        END
        
        -- Phase 1: Validate order
        IF NOT EXISTS (SELECT 1 FROM Orders.Orders WHERE OrderID = @OrderID)
            RAISERROR('Order not found', 16, 1);
        
        -- Phase 2: Reserve inventory
        -- Use savepoint for partial rollback capability
        SAVE TRANSACTION InventoryReservation;
        
        UPDATE Inventory.Stock
        SET ReservedQty = ReservedQty + ol.Quantity
        FROM Inventory.Stock s
        JOIN Orders.OrderLines ol ON s.ProductID = ol.ProductID
        WHERE ol.OrderID = @OrderID;
        
        -- Phase 3: Process payment (might fail)
        BEGIN TRY
            EXEC Payments.ProcessPayment @OrderID = @OrderID;
        END TRY
        BEGIN CATCH
            -- Payment failed - rollback just the inventory reservation
            ROLLBACK TRANSACTION InventoryReservation;
            
            -- Re-throw the error to outer catch
            THROW;
        END CATCH
        
        -- Phase 4: Update order status
        UPDATE Orders.Orders
        SET Status = 'Confirmed', ConfirmedDate = GETDATE()
        WHERE OrderID = @OrderID;
        
        -- Commit only if we started the transaction
        IF @TranStarted = 1
            COMMIT TRANSACTION;
        
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        -- Handle transaction state
        IF @TranStarted = 1 AND @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
        
        -- Log the error
        EXEC Logs.LogError 
            @ProcName = 'ComplexOrderProcess',
            @ErrorMsg = @ErrorMessage,
            @OrderID = @OrderID;
    END CATCH
END;
GO
 
-- A common pattern: Transaction wrapper procedure
CREATE PROCEDURE Utils.ExecuteWithTransaction
    @SQLToExecute NVARCHAR(MAX),
    @Success BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        EXEC sp_executesql @SQLToExecute;
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
            
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

The @@TRANCOUNT Trap

Raising Custom Errors

Custom errors communicate business rule violations, validation failures, and other application-level issues. They transform silent failures into actionable information.

When to raise custom errors:

Business rule violations ("Cannot process order older than 30 days")
Validation failures ("Invalid discount percentage")
Missing prerequisites ("Customer credit limit not set")
State violations ("Order already shipped")

raise_errors.sql

-- RAISERROR (legacy but widely used)
-- Syntax: RAISERROR (message, severity, state)
-- Severity 16 = user error, caught by TRY/CATCH
-- State 1 = user-defined state (1 is common default)
 
CREATE PROCEDURE Validation.ValidateCustomer
    @CustomerID INT
AS
BEGIN
    DECLARE @CreditLimit DECIMAL(18,2);
    DECLARE @AccountStatus VARCHAR(20);
    
    SELECT @CreditLimit = CreditLimit, @AccountStatus = AccountStatus
    FROM Customers
    WHERE CustomerID = @CustomerID;
    
    -- Not found error
    IF @CreditLimit IS NULL
    BEGIN
        RAISERROR('Customer %d does not exist', 16, 1, @CustomerID);
        RETURN;
    END
    
    -- Business rule errors
    IF @AccountStatus = 'Suspended'
    BEGIN
        RAISERROR('Customer %d account is suspended', 16, 1, @CustomerID);
        RETURN;
    END
    
    IF @CreditLimit <= 0
    BEGIN
        RAISERROR('Customer %d has no credit limit established', 16, 1, @CustomerID);
        RETURN;
    END
END;
GO
 
-- THROW (SQL Server 2012+, preferred)
-- Syntax: THROW error_number, message, state
-- Or just THROW (re-throws current error in CATCH)
 
CREATE PROCEDURE Orders.ValidateOrder
    @OrderID INT,
    @CustomerID INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Validation errors with THROW
    IF @OrderID IS NULL
        THROW 50001, 'OrderID is required', 1;
    
    IF @Amount <= 0
        THROW 50002, 'Order amount must be positive', 1;
    
    -- Check customer exists and has credit
    DECLARE @AvailableCredit DECIMAL(18,2);
    
    SELECT @AvailableCredit = CreditLimit - ISNULL(OutstandingBalance, 0)
    FROM Customers
    WHERE CustomerID = @CustomerID;
    
    IF @AvailableCredit IS NULL
        THROW 50003, 'Customer not found', 1;
    
    IF @Amount > @AvailableCredit
    BEGIN
        DECLARE @ErrorMsg NVARCHAR(500) = 
            CONCAT('Insufficient credit. Required: $', @Amount, 
                   ', Available: $', @AvailableCredit);
        THROW 50004, @ErrorMsg, 1;
    END
END;
GO
 
-- Custom error numbers (50000+ for user-defined)
-- Best practice: Define and document error number ranges
/*
50001-50099: Input validation errors
50100-50199: Business rule errors  
50200-50299: Data integrity errors
50300-50399: External system errors
*/
 
-- Re-throwing errors (preserves original error info)
CREATE PROCEDURE Wrapper.ExecuteWithLogging
    @ProcedureName NVARCHAR(200)
AS
BEGIN
    BEGIN TRY
        EXEC sp_executesql @ProcedureName;
    END TRY
    BEGIN CATCH
        -- Log the error...
        INSERT INTO Logs.Errors (ErrorMessage, ErrorNumber, ProcedureName, ErrorDate)
        VALUES (ERROR_MESSAGE(), ERROR_NUMBER(), @ProcedureName, GETDATE());
        
        -- Re-throw to caller (preserves original error details)
        THROW;  -- No parameters = re-throw current error
    END CATCH
END;

Error Logging Patterns

Production procedures must log errors for debugging and audit purposes. A well-designed error log captures:

What went wrong (error message, code)
Where it happened (procedure, line number)
When it occurred (timestamp)
Who was affected (user, session)
Context (input parameters, state information)

error_logging.sql

SQL Server

-- Error log table
CREATE TABLE Logs.Errors (
    ErrorLogID BIGINT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(200),
    ErrorLine INT,
    ErrorMessage NVARCHAR(4000),
    ErrorDate DATETIME2 DEFAULT SYSDATETIME(),
    UserName NVARCHAR(200) DEFAULT SUSER_SNAME(),
    HostName NVARCHAR(200) DEFAULT HOST_NAME(),
    ApplicationName NVARCHAR(200) DEFAULT APP_NAME(),
    AdditionalContext NVARCHAR(MAX),
    INDEX IX_ErrorDate (ErrorDate)
);
GO
 
-- Reusable error logging procedure
CREATE PROCEDURE Logs.LogError
    @AdditionalContext NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO Logs.Errors (
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine,
        ErrorMessage,
        AdditionalContext
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        @AdditionalContext
    );
    
    -- Return the log ID for reference
    SELECT SCOPE_IDENTITY() AS ErrorLogID;
END;
GO
 
-- Using the logging procedure in a CATCH block
CREATE PROCEDURE Orders.CreateOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT,
    @OrderID INT OUTPUT,
    @ErrorLogID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SET @OrderID = NULL;
    SET @ErrorLogID = NULL;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Create order logic...
        INSERT INTO Orders.Orders (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, GETDATE(), 'New');
        
        SET @OrderID = SCOPE_IDENTITY();
        
        INSERT INTO Orders.OrderLines (OrderID, ProductID, Quantity)
        VALUES (@OrderID, @ProductID, @Quantity);
        
        -- Intentional error for demo (divide by zero)
        DECLARE @Test INT = 1 / 0;
        
        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Build context string
        DECLARE @Context NVARCHAR(MAX) = CONCAT(
            'CustomerID=', @CustomerID, 
            ', ProductID=', @ProductID,
            ', Quantity=', @Quantity,
            ', Attempted OrderID=', @OrderID
        );
        
        -- Log error and capture log ID
        EXEC Logs.LogError @AdditionalContext = @Context;
        SELECT @ErrorLogID = SCOPE_IDENTITY();
        
        SET @OrderID = NULL;
    END CATCH
END;
GO
 
-- Structured error context with JSON
CREATE PROCEDURE Logs.LogErrorWithJson
    @ContextJson NVARCHAR(MAX) = NULL
AS
BEGIN
    INSERT INTO Logs.Errors (
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine,
        ErrorMessage,
        AdditionalContext
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        @ContextJson
    );
END;
GO
 
-- Usage with JSON context
BEGIN TRY
    -- ... code that fails ...
END TRY
BEGIN CATCH
    DECLARE @JsonContext NVARCHAR(MAX) = (
        SELECT 
            @OrderID AS order_id,
            @CustomerID AS customer_id,
            @Amount AS amount,
            GETDATE() AS error_timestamp
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
    
    EXEC Logs.LogErrorWithJson @ContextJson = @JsonContext;
END CATCH

Error Log Retention

Error logs grow quickly. Implement automated archival or purging of old error logs. Consider partitioning by date for efficient maintenance. Index ErrorDate for quick diagnostics of recent issues.

Professional Error Handling Patterns

Production-ready procedures use consistent, standardized error handling patterns. Here are proven patterns used in enterprise systems:

Standard Error Handling Patterns

•Result Pattern — Return success flag, error code, and error message as OUT parameters. Caller checks flag before using results.
•Exception Bubbling — Catch, log, and re-throw. Preserves original error while adding logging.
•Error Transformation — Convert database errors to application-friendly messages while logging technical details.
•Retry Pattern — For transient errors (deadlocks, timeouts), attempt retry with exponential backoff.
•Circuit Breaker — Track failure rates and temporarily disable operations that consistently fail.

error_patterns.sql

SQL Server

-- Pattern 1: Standardized Result Pattern
CREATE TYPE dbo.OperationResult AS TABLE (
    Success BIT,
    ErrorCode VARCHAR(50),
    ErrorMessage NVARCHAR(500),
    ResultData NVARCHAR(MAX)  -- JSON for flexible data
);
GO
 
CREATE PROCEDURE Sales.CreateOrderWithResult
    @CustomerID INT,
    @Items NVARCHAR(MAX),  -- JSON array of items
    @Result OperationResult READONLY OUTPUT  -- Would use table variable in practice
    -- Note: TVPs can't be OUTPUT, so we use this pattern differently:
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Success BIT = 0;
    DECLARE @ErrorCode VARCHAR(50) = NULL;
    DECLARE @ErrorMessage NVARCHAR(500) = NULL;
    DECLARE @OrderID INT = NULL;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validation
        IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID)
        BEGIN
            SET @ErrorCode = 'CUSTOMER_NOT_FOUND';
            SET @ErrorMessage = 'Customer does not exist';
            GOTO ReturnResult;
        END
        
        -- Create order
        INSERT INTO Sales.Orders (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, GETDATE(), 'New');
        SET @OrderID = SCOPE_IDENTITY();
        
        -- Insert items from JSON
        INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
        SELECT @OrderID, ProductID, Quantity, UnitPrice
        FROM OPENJSON(@Items)
        WITH (
            ProductID INT,
            Quantity INT,
            UnitPrice DECIMAL(18,2)
        );
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        
        SET @ErrorCode = 'SYSTEM_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
        
        EXEC Logs.LogError @AdditionalContext = @Items;
    END CATCH
    
    ReturnResult:
    -- Return standardized result
    SELECT 
        @Success AS Success,
        @ErrorCode AS ErrorCode,
        @ErrorMessage AS ErrorMessage,
        CASE WHEN @Success = 1 
            THEN JSON_OBJECT('OrderID': @OrderID)
            ELSE NULL 
        END AS ResultData;
END;
GO
 
-- Pattern 2: Retry with Exponential Backoff
CREATE PROCEDURE Utils.ExecuteWithRetry
    @SQL NVARCHAR(MAX),
    @MaxRetries INT = 3,
    @DelayMs INT = 100,
    @Success BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @RetryCount INT = 0;
    DECLARE @CurrentDelay INT = @DelayMs;
    DECLARE @ErrorMessage NVARCHAR(500);
    
    SET @Success = 0;
    
    WHILE @RetryCount < @MaxRetries AND @Success = 0
    BEGIN
        BEGIN TRY
            EXEC sp_executesql @SQL;
            SET @Success = 1;  -- Success!
        END TRY
        BEGIN CATCH
            SET @RetryCount = @RetryCount + 1;
            SET @ErrorMessage = ERROR_MESSAGE();
            
            -- Only retry on transient errors
            IF ERROR_NUMBER() IN (
                1205,   -- Deadlock
                -2,     -- Timeout
                1222,   -- Lock timeout
                8645,   -- Timeout waiting for memory
                8651    -- Low memory condition
            )
            BEGIN
                IF @RetryCount < @MaxRetries
                BEGIN
                    -- Wait with exponential backoff
                    DECLARE @DelayStr VARCHAR(12) = 
                        '00:00:0' + CAST(@CurrentDelay / 1000.0 AS VARCHAR);
                    WAITFOR DELAY @DelayStr;
                    SET @CurrentDelay = @CurrentDelay * 2;  -- Double for next retry
                END
            END
            ELSE
            BEGIN
                -- Non-transient error, don't retry
                SET @RetryCount = @MaxRetries;  -- Exit loop
            END
        END CATCH
    END
    
    IF @Success = 0
    BEGIN
        -- All retries failed
        EXEC Logs.LogError @AdditionalContext = @SQL;
    END
END;

Summary: Robust Error Handling

We've covered comprehensive error handling for stored procedures. Here are the essential takeaways:

Key Takeaways

•TRY/CATCH blocks are essential — Wrap potentially failing code to handle errors gracefully. Each database has its own syntax.
•Always check transaction state — Use @@TRANCOUNT (or equivalent) before ROLLBACK to avoid errors when no transaction is active.
•Roll back on error — Uncommitted changes after an error leave data in an inconsistent state. Always rollback explicitly.
•Use savepoints for partial rollback — When processing multiple operations, savepoints allow rolling back specific parts while preserving others.
•Raise custom errors — THROW, RAISERROR, RAISE EXCEPTION, or SIGNAL communicate business rule violations clearly.
•Log errors comprehensively — Capture what, where, when, who, and context for every error. Enable debugging without reproducing issues.
•Use consistent patterns — Standardize error handling across all procedures. Result patterns, retry patterns, and circuit breakers provide reliability.
•Test error paths — Error handling code needs testing too. Deliberately trigger errors to verify proper behavior.

Module Complete!

Module Complete