Bank Transfer System

Build a stored procedure that transfers funds between accounts safely inside a transaction.

TransactionsStored ProceduresError Handling

Overview

Transferring money between two accounts is really two writes — subtract from one account, add to the other — and both must succeed or neither should happen. If the debit succeeds but something fails before the credit runs, money simply vanishes from the system. That failure mode is exactly what a database transaction exists to prevent: wrapping both updates in BEGIN TRANSACTION and COMMIT TRANSACTION makes them behave as one indivisible operation, and a ROLLBACK TRANSACTION inside a TRY...CATCH block undoes everything cleanly the moment anything goes wrong — including a simple business-rule failure like insufficient funds.

By the end of this tutorial you will have an accounts table protected by a CHECK constraint that makes a negative balance structurally impossible, a ledger_transactions table recording every debit and credit as an append-only history, and a CREATE PROCEDURE that performs an entire transfer — balance check, debit, credit, and two ledger rows — as one atomic transaction, using THROW to raise a clear custom error and roll back completely if the sender does not have enough funds.

What You'll Build

An accounts table with a CHECK (balance >= 0) constraint preventing negative balances.
A ledger_transactions table acting as an append-only ledger of every debit and credit.
A transfer_funds stored procedure wrapping a transfer in BEGIN TRANSACTION / COMMIT TRANSACTION.
A TRY...CATCH block with ROLLBACK TRANSACTION and THROW on insufficient funds.
Verification queries proving account balances and the ledger stay consistent after a transfer.

Prerequisites

CREATE TABLE basics in T-SQL — columns, data types, NOT NULL, and DEFAULT values.
Primary keys and foreign keys — how FOREIGN KEY ... REFERENCES links two tables.
Basic SELECT, INSERT, and UPDATE statements.
The general idea of a transaction — a group of statements that should all succeed or all fail together.
Basic stored procedure syntax — CREATE PROCEDURE ... AS BEGIN ... END and @parameter declarations.

Project Structure

accounts holds the current balance for every account — the single source of truth for "how much money does this account have right now." ledger_transactions never gets updated once a row is written; it only ever grows, recording history the way a real bank statement does. The stored procedure is what keeps the two in sync: every change to a balance in accounts happens inside the same transaction as the ledger rows that explain why.

Table	Purpose	Key Columns
accounts	Current balance per account	account_id (PK), balance (CHECK >= 0)
ledger_transactions	Append-only ledger of every debit/credit	ledger_id (PK), account_id (FK), transfer_id, entry_type, amount

Step 1: Create the Accounts Table

The CHECK (balance >= 0) constraint is the last line of defense against a negative balance — even if every other safeguard in the application layer somehow failed, SQL Server itself will refuse an UPDATE that would push balance below zero. DECIMAL(12,2), not FLOAT, is used for money throughout this schema, because binary floating-point types cannot represent most decimal fractions exactly and will eventually produce off-by-a-cent errors a bank cannot tolerate.

CREATE TABLE accounts (
    account_id INT IDENTITY(1,1) PRIMARY KEY,      -- Surrogate key referenced by ledger_transactions.account_id
    owner_name NVARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,   -- DECIMAL, not FLOAT: exact arithmetic, no rounding drift on money
    CONSTRAINT CK_accounts_balance CHECK (balance >= 0)  -- The database itself refuses to let balance go negative
);

Step 2: Create the Transactions Ledger

Every transfer produces exactly two ledger rows — a DEBIT on the sending account and a CREDIT on the receiving account — sharing the same transfer_id so they can always be found and matched back together later. amount is stored as a positive number in every row; whether it increased or decreased a balance is what entry_type records, not the sign of amount, which keeps every value in this append-only table simple to reason about.

CREATE TABLE ledger_transactions (
    ledger_id INT IDENTITY(1,1) PRIMARY KEY,       -- Own surrogate key for this individual ledger entry
    account_id INT NOT NULL,                       -- FK: which account this entry affects
    transfer_id UNIQUEIDENTIFIER NOT NULL,         -- Links the DEBIT and CREDIT rows from the same transfer together
    entry_type VARCHAR(6) NOT NULL,                -- 'DEBIT' or 'CREDIT' -- whether this entry decreased or increased the balance
    amount DECIMAL(12,2) NOT NULL,                 -- Always positive; entry_type says the direction, not the sign
    created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_ledger_accounts FOREIGN KEY (account_id)
        REFERENCES accounts(account_id) ON DELETE NO ACTION,   -- History can't be orphaned by deleting an account
    CONSTRAINT CK_ledger_type CHECK (entry_type IN ('DEBIT', 'CREDIT')),  -- Only these two values are meaningful here
    CONSTRAINT CK_ledger_amount CHECK (amount > 0)              -- A zero or negative ledger entry would be meaningless
);

Step 3: Insert Sample Accounts

Two accounts to transfer money between — one with a healthy balance, and one deliberately low, so the stored procedure's insufficient-funds path can be demonstrated in Step 5.

INSERT INTO accounts (owner_name, balance) VALUES
('Vikram Singh', 5000.00),
('Ananya Desai', 150.00);

Step 4: Write a Fund Transfer Stored Procedure

TRY...CATCH is T-SQL's structured error-handling block: SQL Server runs everything inside BEGIN TRY and jumps straight to BEGIN CATCH the instant any statement fails, which is where the transaction gets rolled back and a clear error is raised with THROW. SELECT ... WITH (UPDLOCK, ROWLOCK) locks the sender's row for the rest of the transaction, so a second transfer running at the same time cannot read a stale balance and let two transfers both succeed against funds that only cover one of them. If the balance check fails, the procedure rolls back and throws; if it passes, both balance updates and both ledger rows commit together as one atomic unit.

CREATE PROCEDURE transfer_funds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;   -- Suppresses the "N rows affected" messages so callers get clean results
    BEGIN TRY
        BEGIN TRANSACTION;   -- Everything from here to COMMIT/ROLLBACK happens as one atomic unit

        DECLARE @current_balance DECIMAL(12,2);
        DECLARE @transfer_id UNIQUEIDENTIFIER = NEWID();  -- One shared ID linking this transfer's two ledger rows

        -- WITH (UPDLOCK, ROWLOCK) locks this row until COMMIT/ROLLBACK, so a concurrent
        -- transfer from the same account can't read the balance before this one finishes
        SELECT @current_balance = balance
        FROM accounts WITH (UPDLOCK, ROWLOCK)
        WHERE account_id = @from_account;

        IF @current_balance IS NULL
        BEGIN
            THROW 51000, 'Source account does not exist', 1;
        END

        IF @current_balance < @amount
        BEGIN
            THROW 51001, 'Insufficient funds for this transfer', 1;
        END

        -- Debit the sender
        UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
        -- Credit the receiver
        UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@from_account, @transfer_id, 'DEBIT', @amount);

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@to_account, @transfer_id, 'CREDIT', @amount);

        COMMIT TRANSACTION;   -- Both balance updates and both ledger rows become permanent together
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;   -- Undo everything; nothing written so far takes effect

        THROW;   -- Re-raise the original error so the caller sees exactly what went wrong
    END CATCH
END;

Why the balance CHECK constraint still matters here

The IF @current_balance < @amount check stops most overdrafts, but the table-level CHECK (balance >= 0) constraint from Step 1 is what guarantees it structurally — even a future bug in this procedure, or a totally different piece of code updating accounts directly, still cannot push a balance negative.

Step 5: Execute the Procedure and Verify the Ledger

A successful transfer from Vikram to Ananya, followed by a failed one attempted from Ananya's now-smaller balance against too large an amount — showing both the commit path and the rollback-and-throw path in the same procedure.

EXEC transfer_funds @from_account = 1, @to_account = 2, @amount = 500.00;  -- Vikram sends 500.00 to Ananya: succeeds

SELECT account_id, owner_name, balance FROM accounts ORDER BY account_id;

account_id	owner_name	balance
1	Vikram Singh	4500.00
2	Ananya Desai	650.00

EXEC transfer_funds @from_account = 2, @to_account = 1, @amount = 10000.00;  -- Ananya tries to send far more than her balance: rejected

Error Raised by THROW

Click Run to see what this code prints.

Complete Schema

The full schema and stored procedure, ready to run top to bottom.

CREATE TABLE accounts (
    account_id INT IDENTITY(1,1) PRIMARY KEY,
    owner_name NVARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT CK_accounts_balance CHECK (balance >= 0)
);

CREATE TABLE ledger_transactions (
    ledger_id INT IDENTITY(1,1) PRIMARY KEY,
    account_id INT NOT NULL,
    transfer_id UNIQUEIDENTIFIER NOT NULL,
    entry_type VARCHAR(6) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_ledger_accounts FOREIGN KEY (account_id)
        REFERENCES accounts(account_id) ON DELETE NO ACTION,
    CONSTRAINT CK_ledger_type CHECK (entry_type IN ('DEBIT', 'CREDIT')),
    CONSTRAINT CK_ledger_amount CHECK (amount > 0)
);
GO

CREATE PROCEDURE transfer_funds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @current_balance DECIMAL(12,2);
        DECLARE @transfer_id UNIQUEIDENTIFIER = NEWID();

        SELECT @current_balance = balance
        FROM accounts WITH (UPDLOCK, ROWLOCK)
        WHERE account_id = @from_account;

        IF @current_balance IS NULL
        BEGIN
            THROW 51000, 'Source account does not exist', 1;
        END

        IF @current_balance < @amount
        BEGIN
            THROW 51001, 'Insufficient funds for this transfer', 1;
        END

        UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
        UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@from_account, @transfer_id, 'DEBIT', @amount);

        INSERT INTO ledger_transactions (account_id, transfer_id, entry_type, amount)
        VALUES (@to_account, @transfer_id, 'CREDIT', @amount);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH
END;

Sample Queries

Two queries that prove the ledger and the account balances agree with each other — which is exactly what the atomic procedure guarantees will always be true.

-- Full transaction history for one account, most recent first
SELECT ledger_id, transfer_id, entry_type, amount, created_at
FROM ledger_transactions
WHERE account_id = 2
ORDER BY created_at DESC;

ledger_id	transfer_id	entry_type	amount	created_at
2	3F2504E0-...	CREDIT	500.00	2026-08-09 10:15:00

-- Reconcile: an account's stored balance should always equal its starting balance
-- plus every CREDIT minus every DEBIT recorded against it in the ledger
SELECT
    a.account_id,
    a.owner_name,
    a.balance AS stored_balance,
    COALESCE(SUM(CASE WHEN t.entry_type = 'CREDIT' THEN t.amount ELSE -t.amount END), 0) AS net_ledger_movement
FROM accounts a
LEFT JOIN ledger_transactions t ON a.account_id = t.account_id
GROUP BY a.account_id, a.owner_name, a.balance;

account_id	owner_name	stored_balance	net_ledger_movement
1	Vikram Singh	4500.00	-500.00
2	Ananya Desai	650.00	500.00

Extend This Project

Add an account_type column ('checking'/'savings') and enforce different overdraft rules per type.
Add a daily_transfer_limit column to accounts and check it inside transfer_funds before allowing a transfer.
Set the transaction isolation level explicitly with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for stricter consistency.
Add a composite index on ledger_transactions(account_id, created_at) to speed up per-account statement queries.
Add an audit trigger that writes a row to a separate audit_log table on every call to transfer_funds.

Summary

You built a schema where a CHECK constraint makes a negative balance structurally impossible, and a stored procedure makes a two-account transfer atomic: BEGIN TRANSACTION groups the balance check, both updates, and both ledger inserts into one unit, COMMIT TRANSACTION makes them permanent together, and a TRY...CATCH block with ROLLBACK TRANSACTION and THROW undoes everything and raises a clear error the moment the balance check fails. That combination — a business-rule check, a row lock to prevent a race with concurrent transfers, and an all-or-nothing commit — is the same shape every real payment or transfer system relies on, whether it moves money, inventory, or any other value that must never be created or destroyed by a half-finished operation.