Testing a stored procedure is not just "call it and see if it works." You need a systematic approach. Think of it like testing an API endpoint — you check happy path, error path, boundary values, and side effects. Same discipline applies here.
Read the procedure code — understand what it does, what tables it touches, what it returns
Set up test data — insert known data into the tables the procedure reads from
Record the "before" state — SELECT from all affected tables and note the values
Execute the procedure — CALL with your test inputs
Check the output — verify OUT parameters and result sets
Check side effects — SELECT from all tables the procedure modifies (inserts, updates, deletes)
Verify the "after" state matches your expected state exactly
Clean up test data — DELETE or ROLLBACK to keep your test environment clean
| Test Type | What You Test | Example |
|---|---|---|
| Positive / Happy Path | Valid inputs, expected flow | Transfer Rs 500 between two valid accounts with sufficient balance |
| Negative | Invalid inputs, error handling | Transfer from a non-existent account, insufficient balance |
| Boundary | Edge values | Transfer exact balance (zero remaining), transfer Rs 0.01, transfer max allowed amount |
| NULL Handling | NULL in each parameter | Pass NULL as account_id — should the procedure reject it or handle gracefully? |
| Data Integrity | Side effects are correct | After transfer — audit log created? Balances updated atomically? |
| Concurrency | Two calls at the same time | Two transfers from the same account simultaneously — does the balance go negative? |
-- Setup: Known test data
INSERT INTO accounts (account_id, holder_name, balance, status)
VALUES ('TEST001', 'Amit Kumar', 10000.00, 'ACTIVE'),
('TEST002', 'Priya Sharma', 5000.00, 'ACTIVE');
-- Before state
SELECT account_id, balance FROM accounts WHERE account_id IN ('TEST001', 'TEST002');
-- TEST001 = 10000.00, TEST002 = 5000.00
-- Execute
CALL transfer_funds('TEST001', 'TEST002', 2000.00);
-- Verify balances
SELECT account_id, balance FROM accounts WHERE account_id IN ('TEST001', 'TEST002');
-- Expected: TEST001 = 8000.00, TEST002 = 7000.00
-- Verify transaction log
SELECT from_account, to_account, amount, transaction_date
FROM transactions
WHERE from_account = 'TEST001' AND to_account = 'TEST002'
ORDER BY transaction_date DESC LIMIT 1;
-- Expected: amount = 2000.00, transaction_date = today-- Test 1: Insufficient balance
CALL transfer_funds('TEST001', 'TEST002', 999999.00);
-- Expected: ERROR 1644 (45000): Insufficient funds
-- Verify: Both balances UNCHANGED
-- Test 2: Non-existent source account
CALL transfer_funds('FAKE_ACC', 'TEST002', 100.00);
-- Expected: ERROR — account not found
-- Verify: TEST002 balance UNCHANGED
-- Test 3: Transfer to self
CALL transfer_funds('TEST001', 'TEST001', 500.00);
-- Expected: ERROR or no-op (depends on business rule)
-- Verify: Balance unchanged, no transaction log entry
-- Test 4: Negative amount
CALL transfer_funds('TEST001', 'TEST002', -1000.00);
-- Expected: ERROR — amount must be positive
-- If this SUCCEEDS, you found a critical bug
-- Test 5: Zero amount
CALL transfer_funds('TEST001', 'TEST002', 0.00);
-- Expected: ERROR — transfer amount must be greater than zero-- Boundary 1: Transfer EXACT balance (balance becomes zero)
CALL transfer_funds('TEST001', 'TEST002', 10000.00);
-- Expected: SUCCESS — balance should be exactly 0.00
SELECT balance FROM accounts WHERE account_id = 'TEST001';
-- Should be 0.00, not NULL, not negative
-- Boundary 2: Smallest possible amount
CALL transfer_funds('TEST001', 'TEST002', 0.01);
-- Expected: SUCCESS
-- Boundary 3: Maximum decimal precision
CALL transfer_funds('TEST001', 'TEST002', 12345678.99);
-- Expected: Depends on DECIMAL(10,2) — might overflow
-- Boundary 4: Transfer one paisa more than balance
CALL transfer_funds('TEST001', 'TEST002', 10000.01);
-- Expected: ERROR — insufficient funds
-- This catches off-by-one bugs in the >= vs > comparisonAlways test boundary + 1 and boundary - 1. If the procedure checks balance >= amount, test with exact balance AND exact balance + 0.01. That one paisa difference catches comparison operator bugs.
Q: How do you test a stored procedure systematically?
A: I follow a structured approach: (1) Read the procedure code to understand inputs, outputs, and tables affected. (2) Set up test data with known values. (3) Record the before state. (4) Execute the procedure. (5) Verify output parameters and result sets. (6) Check all side effects — inserts, updates, deletes in related tables. (7) Verify the after state. I cover positive tests, negative tests (invalid inputs, error paths), boundary tests (edge values like zero, max, exact limit), NULL handling, and data integrity checks. For critical procedures, I also test concurrent execution.
Key Point: Test stored procedures like APIs — cover happy path, error path, boundary values, NULL handling, and side effects. Always check the before and after state of every affected table.