Stop 05 · SQL & Databases for QATopics · 6/8

Stored Procedures Testing (Overview)

In short

Some systems keep business logic inside the database as stored procedures. When fees, interest or stock updates live there, you test them like any other function.

Testing pre-written SQL routines stored in the database that encapsulate business logic — less common than application-layer testing, but essential in systems that push significant logic into the database itself.

The purple blob presses a button on a big office coffee machine as a cup fills below.

Think of it like this

The office coffee machine

Press 'cappuccino' and the machine runs its fixed recipe inside: grind, steam, pour. You can't see the steps, but you can test it: put in known inputs, check the cup, and check the milk tank went down.

Watch it happen

Testing one stored procedure

Arrange

Call

Check output

Check tables

Rollback

1/5Start a transaction and insert a known loan: a book returned 3 days late.

The big ideas

What you need to know

Treat it like a function

Known inputs in, expected outputs and table changes out. Design cases with partitions and boundaries.

Test the error paths

Unknown ids, NULL inputs and duplicate calls should fail safely, not half-update data.

Transactions keep it clean

BEGIN and ROLLBACK let you test on a shared database without leaving changes behind.

See it in code

Walk through the code

sql

1BEGIN;2 3-- Arrange: a known loan, returned 3 days late4INSERT INTO loans (id, member_id, due_date, returned_on)5VALUES (900, 7, '2026-01-10', '2026-01-13');6 7-- Act: run the procedure under test8CALL apply_late_fee(900);9 10-- Assert: expect Rs 15 (Rs 5 per late day)11SELECT late_fee FROM loans WHERE id = 900;12 13ROLLBACK;  -- leave the database exactly as it was

1/4Open a transaction and insert data you fully control.

Arrange, act, assert, roll back. CALL works in PostgreSQL 11+ and MySQL. The fee rule here is a made-up example.

Save yourself the pain

Common mistakes

Oops

Testing procedures only through the UI that calls them.

Do this instead

Call them directly too. It isolates the logic and makes edge cases easy to try.

Oops

Leaving test rows behind on a shared database.

Do this instead

Wrap tests in a transaction and roll back, or clean up with a script.

Your mission

Test a tiny procedure

~40 min

Quick check

Why wrap a stored procedure test in BEGIN ... ROLLBACK?

Go deeper

1Read a sample stored procedure and identify what business logic it implements.
2Learn why testing stored procedures directly matters in database-logic-heavy systems.
3Understand this is a more specialized skill, valuable in certain enterprise/legacy environments.