Skip to content
elephantoo

Stored functions

Lesson 25 of 31 12 min read

CREATE FUNCTION, DETERMINISTIC, using your own functions in queries and when to prefer procedures.

A stored function is a user-defined function written in SQL. Unlike a procedure, it returns a single value and can be used anywhere an expression is allowed: in SELECT lists, WHERE clauses, ORDER BY and UPDATE ... SET. Functions are great for packaging a calculation or business rule that many queries share.

Creating a function#

SQL
DELIMITER $$

CREATE FUNCTION gst_inclusive(price DECIMAL(10,2), rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN ROUND(price * (1 + rate / 100), 2);
END $$

DELIMITER ;

SELECT gst_inclusive(100.00, 18) AS with_gst, gst_inclusive(499.99, 5) AS with_gst_5;
Output
+----------+------------+
| with_gst | with_gst_5 |
+----------+------------+
|   118.00 |     524.99 |
+----------+------------+

The parts:

  • RETURNS type: the type of the single value returned.
  • Function parameters are always IN, so you don't write IN/OUT.
  • RETURN expr ends the function and gives back the value.
  • Characteristics describe the function to MySQL:
    • DETERMINISTIC: the same inputs always give the same result (NOT DETERMINISTIC is the default).
    • NO SQL: doesn't touch tables. READS SQL DATA: reads but doesn't modify. MODIFIES SQL DATA: writes.

For a one-line function you can skip BEGIN ... END (and the DELIMITER change):

SQL
CREATE FUNCTION initials(full_name VARCHAR(100)) RETURNS VARCHAR(10) DETERMINISTIC NO SQL
RETURN CONCAT(LEFT(full_name, 1), '.', LEFT(SUBSTRING_INDEX(full_name, ' ', -1), 1), '.');

SELECT initials('Ada Lovelace') AS i;
Output
+------+
| i    |
+------+
| A.L. |
+------+

Why the characteristics matter#

When binary logging is enabled (the default in MySQL 8, and required for replication and point-in-time recovery), MySQL refuses to create a function unless you declare it DETERMINISTIC, NO SQL or READS SQL DATA:

SQL
CREATE FUNCTION random_discount() RETURNS INT
RETURN FLOOR(RAND() * 10);
Output
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)

This protects replicas: a non-deterministic function that modifies data could produce different results when the binary log is replayed. Declare characteristics honestly. Lying (marking a RAND()-based function as DETERMINISTIC) can make replicas drift or give wrong results. An administrator can relax the rule with SET GLOBAL log_bin_trust_function_creators = 1;, but honest declarations are better:

SQL
CREATE FUNCTION random_discount() RETURNS INT NOT DETERMINISTIC NO SQL
RETURN FLOOR(RAND() * 10);

Functions that read tables#

SQL
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(30), country CHAR(2));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2), ordered_on DATE);
INSERT INTO customers VALUES (1, 'Asha', 'IN'), (2, 'Rohan', 'IN'), (3, 'Mia', 'DE');
INSERT INTO orders VALUES
    (1, 1, 1200.00, '2026-01-10'), (2, 1, 3400.00, '2026-03-02'),
    (3, 2,  300.00, '2026-02-14'), (4, 3, 9100.00, '2026-02-20');

DELIMITER $$
CREATE FUNCTION customer_tier(p_customer_id INT)
RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
    DECLARE v_spent DECIMAL(12,2);

    SELECT COALESCE(SUM(total), 0) INTO v_spent
    FROM orders WHERE customer_id = p_customer_id;

    RETURN CASE
        WHEN v_spent >= 5000 THEN 'gold'
        WHEN v_spent >= 1000 THEN 'silver'
        ELSE 'bronze'
    END;
END $$
DELIMITER ;

SELECT name, customer_tier(id) AS tier
FROM customers
ORDER BY FIELD(customer_tier(id), 'gold', 'silver', 'bronze'), name;
Output
+-------+--------+
| name  | tier   |
+-------+--------+
| Mia   | gold   |
| Asha  | silver |
| Rohan | bronze |
+-------+--------+

Neat, but be aware of the cost. This function runs a query for every row it's called on, and twice per row here (once in SELECT, once in ORDER BY). On a table of a million customers, that's millions of small queries, and the optimiser can't see inside the function to combine them. A join with GROUP BY does the same work in a single pass:

SQL
SELECT c.name,
       CASE WHEN COALESCE(SUM(o.total), 0) >= 5000 THEN 'gold'
            WHEN COALESCE(SUM(o.total), 0) >= 1000 THEN 'silver'
            ELSE 'bronze' END AS tier
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;
Output
+-------+--------+
| name  | tier   |
+-------+--------+
| Asha  | silver |
| Mia   | gold   |
| Rohan | bronze |
+-------+--------+

Rule of thumb: functions that only compute from their arguments (NO SQL) are cheap and great. Functions that query tables are convenient for single-row lookups but risky in queries over many rows.

Using functions in WHERE and indexes#

SQL
SELECT id, total, gst_inclusive(total, 18) AS gross
FROM orders
WHERE gst_inclusive(total, 18) > 3000;
Output
+----+---------+----------+
| id | total   | gross    |
+----+---------+----------+
|  2 | 3400.00 |  4012.00 |
|  4 | 9100.00 | 10738.00 |
+----+---------+----------+

As with built-in functions, wrapping a column in a function in WHERE prevents a normal index from being used. MySQL 8's functional indexes only accept built-in deterministic functions, not stored functions. For a computed value you filter on often, store it in a generated column instead:

SQL
ALTER TABLE orders
    ADD COLUMN total_gross DECIMAL(10,2) AS (ROUND(total * 1.18, 2)) STORED,
    ADD INDEX idx_total_gross (total_gross);

Restrictions#

Inside a stored function you can't:

  • start, commit or roll back transactions,
  • return a result set (a bare SELECT without INTO),
  • use dynamic SQL (PREPARE/EXECUTE),
  • modify the table the calling statement is reading from.

If you need any of these, write a procedure.

Managing functions#

SQL
SHOW FUNCTION STATUS WHERE Db = DATABASE();
SHOW CREATE FUNCTION customer_tier\G
DROP FUNCTION IF EXISTS initials;

Callers need the EXECUTE privilege, and creators need CREATE ROUTINE. Like procedures, functions run with SQL SECURITY DEFINER by default.

Function or procedure?#

NeedUse
A value inside a query (SELECT f(x))function
Several statements, transactions, result setsprocedure
Output via several parametersprocedure (OUT params)
Reusable pure calculationfunction (DETERMINISTIC NO SQL)

What's next#

Functions and procedures run when you call them. Triggers run automatically when rows change, and events run on a schedule. That's next.

Check your understanding

Quick quiz

0/3 answered

  1. 1.What is the key practical difference between a stored function and a stored procedure?

  2. 2.With binary logging on, creating a function without DETERMINISTIC, NO SQL or READS SQL DATA fails with error 1418. Why?

  3. 3.Which statement is NOT allowed inside a stored function?

Finished reading?

Mark this lesson complete to track your progress.