13. Error handling and transactions in T-SQL

CREATE OR ALTER PROCEDURE dbo.usp_TransferFunds @From INT, @To INT, @Amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                     -- any runtime error rolls back the whole transaction
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @From AND Balance >= @Amount;
            IF @@ROWCOUNT = 0 THROW 50001, 'Insufficient funds or account not found', 1;
            UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @To;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        INSERT INTO dbo.ErrorLog (Number, Message, Proc, Line, LoggedAt)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), SYSUTCDATETIME());
        THROW;                             -- re-raise to the caller
    END CATCH
END;