Real Example: Fund Transfer Procedure

-- Accounts table
CREATE TABLE accounts (
  account_id VARCHAR(20) PRIMARY KEY,
  holder_name VARCHAR(100) NOT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('ACTIVE', 'FROZEN', 'CLOSED') NOT NULL DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CHECK (balance >= 0)
);

-- Transaction log
CREATE TABLE transaction_log (
  txn_id INT AUTO_INCREMENT PRIMARY KEY,
  from_account VARCHAR(20) NOT NULL,
  to_account VARCHAR(20) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('SUCCESS', 'FAILED') NOT NULL,
  error_message VARCHAR(255),
  txn_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_account) REFERENCES accounts(account_id),
  FOREIGN KEY (to_account) REFERENCES accounts(account_id)
);

-- Seed test data
INSERT INTO accounts VALUES
  ('ACC001', 'Rajesh Verma', 50000.00, 'ACTIVE', NOW()),
  ('ACC002', 'Sneha Patil', 25000.00, 'ACTIVE', NOW()),
  ('ACC003', 'Amit Shah', 0.00, 'FROZEN', NOW());

DELIMITER //

CREATE PROCEDURE transfer_funds(
  IN p_from VARCHAR(20),
  IN p_to VARCHAR(20),
  IN p_amount DECIMAL(12,2),
  OUT p_txn_id INT,
  OUT p_result VARCHAR(100)
)
BEGIN
  DECLARE v_from_balance DECIMAL(12,2);
  DECLARE v_from_status VARCHAR(20);
  DECLARE v_to_status VARCHAR(20);
  DECLARE v_error_msg VARCHAR(255);

  -- Start transaction
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_result = 'FAILED: Database error';
    INSERT INTO transaction_log (from_account, to_account, amount, status, error_message)
    VALUES (p_from, p_to, p_amount, 'FAILED', 'Database error');
  END;

  START TRANSACTION;

  -- Validation 1: Amount must be positive
  IF p_amount <= 0 THEN
    SET v_error_msg = 'Amount must be greater than zero';
    INSERT INTO transaction_log (from_account, to_account, amount, status, error_message)
    VALUES (p_from, p_to, p_amount, 'FAILED', v_error_msg);
    SET p_result = CONCAT('FAILED: ', v_error_msg);
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be greater than zero';
  END IF;

  -- Validation 2: Cannot transfer to self
  IF p_from = p_to THEN
    SET v_error_msg = 'Cannot transfer to the same account';
    INSERT INTO transaction_log (from_account, to_account, amount, status, error_message)
    VALUES (p_from, p_to, p_amount, 'FAILED', v_error_msg);
    SET p_result = CONCAT('FAILED: ', v_error_msg);
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot transfer to the same account';
  END IF;

  -- Validation 3: Source account exists and is active
  SELECT balance, status INTO v_from_balance, v_from_status
  FROM accounts WHERE account_id = p_from FOR UPDATE;

  IF v_from_status IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
  END IF;

  IF v_from_status != 'ACTIVE' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account is not active';
  END IF;

  -- Validation 4: Destination account exists and is active
  SELECT status INTO v_to_status
  FROM accounts WHERE account_id = p_to FOR UPDATE;

  IF v_to_status IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
  END IF;

  IF v_to_status != 'ACTIVE' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account is not active';
  END IF;

  -- Validation 5: Sufficient balance
  IF v_from_balance < p_amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;

  -- Execute transfer
  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;

  -- Log success
  INSERT INTO transaction_log (from_account, to_account, amount, status)
  VALUES (p_from, p_to, p_amount, 'SUCCESS');

  SET p_txn_id = LAST_INSERT_ID();
  SET p_result = 'SUCCESS';

  COMMIT;
END //

DELIMITER ;

-- =============================================
-- TEST 1: Happy Path — Valid transfer
-- =============================================
CALL transfer_funds('ACC001', 'ACC002', 5000.00, @txn, @result);
SELECT @txn AS txn_id, @result AS result;
-- Expected: result = 'SUCCESS'

SELECT account_id, balance FROM accounts WHERE account_id IN ('ACC001', 'ACC002');
-- Expected: ACC001 = 45000.00, ACC002 = 30000.00

SELECT * FROM transaction_log WHERE txn_id = @txn;
-- Expected: status = 'SUCCESS', amount = 5000.00

-- =============================================
-- TEST 2: Insufficient funds
-- =============================================
CALL transfer_funds('ACC001', 'ACC002', 999999.00, @txn, @result);
-- Expected: ERROR 1644 — Insufficient funds
-- Verify: Balances unchanged

-- =============================================
-- TEST 3: Frozen account
-- =============================================
CALL transfer_funds('ACC003', 'ACC002', 100.00, @txn, @result);
-- Expected: ERROR — Source account is not active

CALL transfer_funds('ACC001', 'ACC003', 100.00, @txn, @result);
-- Expected: ERROR — Destination account is not active

-- =============================================
-- TEST 4: Non-existent account
-- =============================================
CALL transfer_funds('FAKE123', 'ACC002', 100.00, @txn, @result);
-- Expected: ERROR — Source account not found

-- =============================================
-- TEST 5: Negative amount
-- =============================================
CALL transfer_funds('ACC001', 'ACC002', -500.00, @txn, @result);
-- Expected: ERROR — Amount must be greater than zero

-- =============================================
-- TEST 6: Transfer to self
-- =============================================
CALL transfer_funds('ACC001', 'ACC001', 1000.00, @txn, @result);
-- Expected: ERROR — Cannot transfer to the same account

-- =============================================
-- TEST 7: Exact balance transfer
-- =============================================
-- Reset ACC001 to known value first
UPDATE accounts SET balance = 1000.00 WHERE account_id = 'ACC001';
CALL transfer_funds('ACC001', 'ACC002', 1000.00, @txn, @result);
SELECT balance FROM accounts WHERE account_id = 'ACC001';
-- Expected: balance = 0.00 exactly