Transactions and Error Handling in SQL

Transactions

A transaction is a sequence of one or more SQL operations considered as a whole. It ensures that either all of the operations are executed or none of them are. They are controlled using the BEGIN TRANSACTION, COMMIT, and ROLLBACK commands. This provides a safeguard mechanism, ensuring the integrity of the database by making sure that sets of related operations either fully complete or fully fail.

TRY-CATCH

The TRY-CATCH construct in SQL Server provides a mechanism to gracefully handle exceptions in T-SQL code. When an error occurs inside a TRY block, control is passed to another group of statements that is enclosed in a CATCH block. This offers an opportunity not just to roll back transactions in case of errors, but also to log and address these errors appropriately.

Importance of Error Logging

Logging errors is a fundamental aspect of robust application design. By maintaining a record of errors, developers can troubleshoot and understand the nature of problems that arise during execution. This historical view aids in identifying patterns, preventing future errors, and enhancing overall system reliability.

ErrorLog Table Definition


                            CREATE TABLE ErrorLog (
                                LogID INT PRIMARY KEY IDENTITY(1,1),
                                ErrorMessage NVARCHAR(MAX),
                                ErrorNumber INT,
                                ErrorProcedure NVARCHAR(128),
                                ErrorLine INT,
                                ErrorSeverity INT,
                                ErrorState INT,
                                LogDate DATETIME DEFAULT GETDATE()
                            );

The table above is used to capture and store error details for further analysis.

Stored Procedure: Transaction with Error Handling and Logging


                            CREATE PROCEDURE TransferMoney
                                @SourceAccountID INT,
                                @TargetAccountID INT,
                                @Amount MONEY
                            AS
                            BEGIN
                                BEGIN TRY
                                    BEGIN TRANSACTION;
                            
                                    -- Debit the source account
                                    UPDATE Accounts 
                                    SET Balance = Balance - @Amount
                                    WHERE AccountID = @SourceAccountID;
                            
                                    -- Credit the target account
                                    UPDATE Accounts 
                                    SET Balance = Balance + @Amount
                                    WHERE AccountID = @TargetAccountID;
                            
                                    -- Commit the transaction
                                    COMMIT;
                                END TRY
                                BEGIN CATCH
                                    -- Rollback the transaction in case of an error
                                    ROLLBACK;
                            
                                    -- Log the error details into the ErrorLog table
                                    INSERT INTO ErrorLog (ErrorMessage, ErrorNumber, ErrorProcedure, ErrorLine, ErrorSeverity, ErrorState)
                                    VALUES (ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE());
                            
                                    -- Rethrow the error
                                    THROW;
                                END CATCH
                            END;

In the stored procedure above, we encapsulate the logic of transferring money between two accounts within a transaction. If any error occurs during the operation, the transaction will be rolled back and the error details will be captured in the ErrorLog table for further investigation.