MySQL Stored Functions

In MySQL Stored Procedures you learned stored procedures — SQL statements saved on the server under one name. Now let's look at stored functions: their close cousin that returns a single value and can be used right inside your SQL expressions.

What Are Stored Functions?
Creating a Function
Function Parameters & Return Values
DETERMINISTIC, NO SQL, READS SQL DATA
Calling a Function
Dropping a Function
Procedure vs Function — Which One?
Putting It All Together

What Are Stored Functions?

A stored function is a reusable block of SQL that:

Returns a single value (a number, string, date, etc.)
Can be used inside SQL — inside SELECT, WHERE, SET, or anywhere an expression fits
Cannot use CALL — it's called like a built-in function: SELECT my_function(x)

SELECT id, name, discount_price(price, category) AS discounted FROM products;

Creating a Function

DELIMITER //

CREATE FUNCTION function_name(param1 DATATYPE, param2 DATATYPE)
RETURNS return_datatype
[DETERMINISTIC | NO SQL | READS SQL DATA]
BEGIN
    -- function body
    RETURN value;
END //

DELIMITER ;

The big differences from a procedure:

RETURNS — declares what type the function will return (required)
RETURN — sends the value back (a function must have at least one RETURN)
The characteristics like DETERMINISTIC — tells MySQL about the function's behavior

The simplest possible function

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    RETURN CONCAT('Hello, ', name, '!');
END //

DELIMITER ;

SELECT Greet('Alice');  -- → 'Hello, Alice!'

Function Parameters & Return Values

Parameters — IN only

Unlike procedures, function parameters are always IN. You can't use OUT or INOUT with a function — the only way data comes back is through the RETURN value.

DELIMITER //

CREATE FUNCTION Tax(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * (rate / 100);
END //

DELIMITER ;

SELECT Tax(100.00, 18.00);  -- → 18.00

Variables inside functions

Same as procedures — DECLARE, SET, SELECT ... INTO all work:

DELIMITER //

CREATE FUNCTION FullName(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(101);
    SET result = CONCAT(last_name, ', ', first_name);
    RETURN result;
END //

DELIMITER ;

RETURN with complex logic

You can have multiple RETURN statements in different branches, but only one will execute per call:

DELIMITER //

CREATE FUNCTION OrderStatus(status_code INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF status_code = 1 THEN
        RETURN 'Pending';
    ELSEIF status_code = 2 THEN
        RETURN 'Shipped';
    ELSEIF status_code = 3 THEN
        RETURN 'Delivered';
    ELSE
        RETURN 'Unknown';
    END IF;
END //

DELIMITER ;

SELECT OrderStatus(2);  -- → 'Shipped'

DETERMINISTIC, NO SQL, READS SQL DATA

MySQL needs to know what your function does so it can optimize queries that use it. Pick one:

Characteristic	Meaning	When to use
`DETERMINISTIC`	Same inputs → always same output	Pure calculations (tax, discount, formatting)
`NO SQL`	Function doesn't touch the database at all	String manipulation, math, logic
`READS SQL DATA`	Function reads (but doesn't modify) the database	Lookups, checks, aggregations
`MODIFIES SQL DATA`	Function inserts/updates/deletes	⚠️ Rare in functions — usually use a procedure instead

DETERMINISTIC example

DELIMITER //

CREATE FUNCTION CircleArea(radius DECIMAL(10,2))
RETURNS DECIMAL(10,4)
DETERMINISTIC
BEGIN
    RETURN PI() * radius * radius;
END //

DELIMITER ;

PI() → always returns the same value, and radius → always gives the same area. This is DETERMINISTIC.

READS SQL DATA example

DELIMITER //

CREATE FUNCTION ProductName(product_id INT)
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE name_val VARCHAR(100);
    SELECT name INTO name_val FROM products WHERE id = product_id;
    RETURN name_val;
END //

DELIMITER ;

This reads from the database — results can change if the data changes, so it's not DETERMINISTIC.

⚠️ Important: If your function reads or writes data and you forget the characteristic, MySQL might refuse to use it in certain contexts (like view definitions or generated columns). When in doubt:

Use DETERMINISTIC for pure math/string functions
Use READS SQL DATA for lookups
Use NO SQL for functions that don't touch tables

Calling a Function

Functions plug directly into SQL — no CALL keyword needed.

In SELECT

SELECT 
    id, 
    name, 
    price, 
    Tax(price, 18.00) AS tax_amount 
FROM products;

In WHERE

SELECT id, name, email
FROM users
WHERE FullName(first_name, last_name) = 'Doe, John';

In SET

SET @area = CircleArea(5.0);

In INSERT / UPDATE

INSERT INTO order_log (description) 
VALUES (OrderStatus(2));

UPDATE products 
SET price = price + Tax(price, 5.0)
WHERE category = 'electronics';

Functions calling other functions

DELIMITER //

CREATE FUNCTION DiscountedPrice(price DECIMAL(10,2), category VARCHAR(20))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE disc DECIMAL(10,2);
    
    IF category = 'premium' THEN
        SET disc = 0.20;
    ELSE
        SET disc = 0.10;
    END IF;
    
    RETURN price - Tax(price, disc * 100);
END //

DELIMITER ;

Procedures can use functions — a procedure can call a function inside its SQL statements, just like any other expression:

DELIMITER //

CREATE PROCEDURE ShowDiscountedPrices(IN cat VARCHAR(20))
BEGIN
    SELECT id, name, price, DiscountedPrice(price, cat) AS final_price
    FROM products;
END //

DELIMITER ;

Dropping a Function

DROP FUNCTION IF EXISTS Greet;
DROP FUNCTION IF EXISTS Tax;
DROP FUNCTION IF EXISTS CircleArea;

IF EXISTS — same safety net as procedures.

Updating a function

In MySQL 8.0+, same as procedures:

DELIMITER //

CREATE OR REPLACE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    RETURN CONCAT('Hey there, ', name, '!  Good to see you.');
END //

DELIMITER ;

Or the old way: DROP FUNCTION then CREATE FUNCTION.

Procedure vs Function — Which One?

	Procedure	Function
Call it with	`CALL name(...)`	`SELECT name(...)` or inside SQL
Returns	Zero or more result sets, `OUT` params	A single value (`RETURN`)
Use in WHERE	❌ No	✅ Yes
Use in SELECT	❌ No (subquery trick possible but ugly)	✅ Yes
OUT / INOUT params	✅ Yes	❌ No — IN only
Must have RETURN	❌ No	✅ Yes
Transaction support	✅ Yes (COMMIT, ROLLBACK)	❌ Generally not allowed
Can call the other	Can use functions in its SQL	Can `CALL` a procedure (MySQL 8.0+)

When to use a procedure

You need to insert/update/delete multiple tables
You need multiple result sets
You need OUT or INOUT parameters
You need transactions (START TRANSACTION, COMMIT, ROLLBACK)

When to use a function

You need a reusable calculation (tax, discount, age from DOB)
You need a lookup value that you can use inside SELECT / WHERE
You want to simplify complex expressions by wrapping them in a named function

Putting It All Together

Let's build something real — an order system using both procedures and functions together.

Step 1: Functions for calculations

DELIMITER //

CREATE FUNCTION TaxAmount(amount DECIMAL(10,2), tax_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(amount * (tax_rate / 100), 2);
END //

CREATE FUNCTION OrderTotal(order_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(quantity * price) INTO total
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = order_id;
    RETURN IFNULL(total, 0.00);
END //

DELIMITER ;

Step 2: Procedure that uses both functions

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_message VARCHAR(100)
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;

    -- check stock
    SELECT price, quantity INTO v_price, v_stock
    FROM products WHERE id = p_product_id;

    IF v_stock >= p_quantity THEN
        -- insert order
        INSERT INTO orders (user_id, total_amount)
        VALUES (p_user_id, v_price * p_quantity);

        SET p_order_id = LAST_INSERT_ID();

        -- insert order items
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (p_order_id, p_product_id, p_quantity, v_price);

        -- update stock
        UPDATE products
        SET quantity = quantity - p_quantity
        WHERE id = p_product_id;

        -- use the TaxAmount function inside the procedure
        SET p_message = CONCAT(
            'Order placed. Total: $', OrderTotal(p_order_id),
            ' (incl. tax: $', TaxAmount(OrderTotal(p_order_id), 10.00), ')'
        );
    ELSE
        SET p_order_id = 0;
        SET p_message = 'Insufficient stock';
    END IF;
END //

DELIMITER ;

Step 3: Run it

CALL ProcessOrder(1, 5, 2, @order_id, @message);
SELECT @message;

Step 4: Use functions directly in reports

SELECT 
    id AS order_id,
    OrderTotal(id) AS total,
    TaxAmount(OrderTotal(id), 10.00) AS tax,
    OrderTotal(id) + TaxAmount(OrderTotal(id), 10.00) AS grand_total
FROM orders
WHERE user_id = 1;

MySQL Stored Functions — Return Values, Parameters & Best Practices

Table of Contents