Functions and procedures look similar. Both contain SQL logic. Both are stored in the database. But they have important differences that affect how you test them. Think of it this way: a function is a calculator — you give it input, it gives back a result. A procedure is a workflow — it performs a series of actions.
DELIMITER //
CREATE FUNCTION calculate_tax(
p_amount DECIMAL(12,2),
p_tax_rate DECIMAL(5,2)
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
RETURN ROUND(p_amount * p_tax_rate / 100, 2);
END //
DELIMITER ;
-- Functions can be used INSIDE queries — this is the key difference
SELECT order_id,
total_amount,
calculate_tax(total_amount, 18.00) AS gst,
total_amount + calculate_tax(total_amount, 18.00) AS grand_total
FROM orders;
-- You can even use them in WHERE clauses
SELECT * FROM orders
WHERE calculate_tax(total_amount, 18.00) > 5000;| Aspect | Testing a Function | Testing a Procedure |
|---|---|---|
| How to call | SELECT calculate_tax(1000, 18) | CALL transfer_funds(...) |
| Check return value | Directly in SELECT result | Check OUT parameters via @variables |
| Check side effects | Functions should NOT have side effects | Always check — inserts, updates, deletes |
| NULL input | SELECT calculate_tax(NULL, 18) — should return NULL or handle gracefully | CALL proc(NULL, ...) — should raise error or handle |
| Boundary test | calculate_tax(0, 18), calculate_tax(99999999.99, 18) | Similar — test with 0, max, exact boundary |
| In-query testing | Use inside SELECT to verify it works with real data | Not applicable — procedures cannot be used in queries |
-- Test 1: Normal calculation
SELECT calculate_tax(10000.00, 18.00);
-- Expected: 1800.00
-- Test 2: Zero amount
SELECT calculate_tax(0.00, 18.00);
-- Expected: 0.00
-- Test 3: Zero tax rate
SELECT calculate_tax(10000.00, 0.00);
-- Expected: 0.00
-- Test 4: Decimal precision
SELECT calculate_tax(999.99, 18.00);
-- Expected: 180.00 (rounded from 179.9982)
-- Test 5: NULL input
SELECT calculate_tax(NULL, 18.00);
-- Expected: NULL (or error, depending on implementation)
-- Test 6: Large value
SELECT calculate_tax(99999999.99, 18.00);
-- Expected: 17999999.998 → 18000000.00 — check DECIMAL overflow
-- Test 7: Use with real table data
SELECT order_id, total_amount, calculate_tax(total_amount, 18.00) AS tax
FROM orders
WHERE calculate_tax(total_amount, 18.00) != ROUND(total_amount * 0.18, 2);
-- Expected: 0 rows — if any appear, the function has a rounding bugDETERMINISTIC means the function always returns the same output for the same input. MySQL requires you to declare this explicitly. If your function reads from tables that can change, it is NOT deterministic — use READS SQL DATA instead.
Q: What is the difference between a function and a stored procedure in MySQL?
A: A function MUST return a value using the RETURNS clause and can be used inline in SELECT, WHERE, and SET statements — like a calculator. It cannot modify data or use transactions. A stored procedure does not return a value directly — it uses OUT parameters and is called with the CALL statement. It can modify data, use transactions, and perform complex workflows. For testing, functions are tested with SELECT (checking return values and precision), while procedures are tested with CALL followed by verifying OUT parameters and side effects in related tables.
Key Point: Functions return values and work inside SELECT statements. Procedures perform workflows and are called with CALL. Test functions for return values and precision. Test procedures for side effects and data changes.