Both are named blocks of SQL stored on the server, and they serve different purposes. A stored function calculates and returns one value, so you use it inside a query the way you use ROUND() or CONCAT(). A stored procedure carries out a task, often several statements with a transaction, and you run it on its own with CALL. If you need a value inside a SELECT, write a function. If you need to change data in steps, write a procedure.
The differences at a glance
| Stored function | Stored procedure | |
|---|---|---|
| How it runs | Inside an expression: SELECT f(x), WHERE f(x) > 0 | CALL p(...) as its own statement |
| Returns | Exactly one value via RETURN | Nothing, or values through OUT/INOUT parameters, or result sets |
| Parameters | Input only | IN, OUT, INOUT |
| Transactions | Not allowed (no COMMIT/ROLLBACK) | Allowed |
| Returning a result set | Not allowed | Allowed (a plain SELECT inside it) |
Dynamic SQL (PREPARE) | Not allowed | Allowed |
| Binary logging rules | Must declare DETERMINISTIC, NO SQL or READS SQL DATA (unless an admin relaxes it) | No such requirement |
Sample data
CREATE TABLE accounts (
acc_id INT PRIMARY KEY,
holder VARCHAR(20),
balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transfers (
tr_id INT AUTO_INCREMENT PRIMARY KEY,
from_acc INT, to_acc INT, amount DECIMAL(12,2),
done_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts VALUES (1,'Kavya',25000), (2,'Nikhil',4000), (3,'Rahul',12000);
A function: GST on an amount
Bodies contain semicolons, so the mysql client needs a temporary delimiter. MySQL Workbench handles this for you.
DELIMITER //
CREATE FUNCTION gst_amount(base DECIMAL(12,2), rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
BEGIN
RETURN ROUND(base * rate / 100, 2);
END //
DELIMITER ;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
Binary logging is on by default in MySQL 8.0, so MySQL wants to know how the function behaves before it accepts it. This one always gives the same output for the same input and reads no tables, so declare that:
DELIMITER //
CREATE FUNCTION gst_amount(base DECIMAL(12,2), rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL
BEGIN
RETURN ROUND(base * rate / 100, 2);
END //
DELIMITER ;
SELECT gst_amount(1999, 18) AS gst, 1999 + gst_amount(1999, 18) AS total;
| gst | total |
|---|---|
| 359.82 | 2358.82 |
Declare these honestly. Marking a function DETERMINISTIC when it is not (for example, if it uses NOW()) can make replicas drift from the source. A function can also read tables:
DELIMITER //
CREATE FUNCTION balance_band(p_acc INT) RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
DECLARE b DECIMAL(12,2);
SELECT balance INTO b FROM accounts WHERE acc_id = p_acc;
RETURN CASE WHEN b IS NULL THEN 'unknown' WHEN b >= 10000 THEN 'high' ELSE 'low' END;
END //
DELIMITER ;
SELECT holder, balance, balance_band(acc_id) AS band FROM accounts ORDER BY acc_id;
| holder | balance | band |
|---|---|---|
| Kavya | 25000.00 | high |
| Nikhil | 4000.00 | low |
| Rahul | 12000.00 | high |
Be careful with this pattern on big tables: the inner SELECT runs once per row. A join would usually be faster.
A procedure: transfer money safely
DELIMITER //
CREATE PROCEDURE transfer_money(IN p_from INT, IN p_to INT, IN p_amt DECIMAL(12,2),
OUT p_status VARCHAR(40))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_status = 'failed: rolled back';
END;
START TRANSACTION;
UPDATE accounts SET balance = balance - p_amt WHERE acc_id = p_from;
UPDATE accounts SET balance = balance + p_amt WHERE acc_id = p_to;
INSERT INTO transfers (from_acc, to_acc, amount) VALUES (p_from, p_to, p_amt);
COMMIT;
SET p_status = 'ok';
END //
DELIMITER ;
CALL transfer_money(1, 2, 5000, @s); SELECT @s AS status;
CALL transfer_money(2, 3, 50000, @s); SELECT @s AS status;
| status |
|---|
| ok |
| status |
|---|
| failed: rolled back |
SELECT acc_id, holder, balance FROM accounts ORDER BY acc_id;
SELECT tr_id, from_acc, to_acc, amount FROM transfers;
| acc_id | holder | balance |
|---|---|---|
| 1 | Kavya | 20000.00 |
| 2 | Nikhil | 9000.00 |
| 3 | Rahul | 12000.00 |
| tr_id | from_acc | to_acc | amount |
|---|---|---|---|
| 1 | 1 | 2 | 5000.00 |
The second transfer would have pushed Nikhil's balance below zero. The CHECK constraint raised an error, the handler rolled the whole transaction back, and the procedure reported failure through its OUT parameter. A function could not do this: try putting COMMIT in one and MySQL refuses with ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger. Transactions are explained in COMMIT and ROLLBACK explained.
Using one where the other belongs
SELECT transfer_money(1,2,10,@s); -> ERROR 1305 (42000): FUNCTION fp.transfer_money does not exist
CALL gst_amount(100, 18); -> ERROR 1305 (42000): PROCEDURE fp.gst_amount does not exist
Functions and procedures live in separate namespaces (here the database is fp), so the error message says "does not exist" even though a routine with that name does exist. List what you have with:
SELECT ROUTINE_NAME, ROUTINE_TYPE, IS_DETERMINISTIC, SQL_DATA_ACCESS
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'fp'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;
| ROUTINE_NAME | ROUTINE_TYPE | IS_DETERMINISTIC | SQL_DATA_ACCESS |
|---|---|---|---|
| balance_band | FUNCTION | NO | READS SQL DATA |
| gst_amount | FUNCTION | YES | NO SQL |
| transfer_money | PROCEDURE | NO | CONTAINS SQL |
Choosing
- Function: reusable calculations such as tax, age from a date of birth, formatting a code, or a business rule that many queries share.
- Procedure: multi-step changes, batch jobs, anything that needs a transaction or returns several result sets.
- Neither: logic that changes often and needs testing and code review. Many teams keep that in application code and use routines sparingly.
A procedure can call functions, and a procedure can be scheduled with MySQL events. Neither should be confused with triggers, which run automatically on inserts, updates and deletes. For a full procedure walkthrough, see the stored procedures tutorial.