MySQL Stored Functions

Master MySQL stored functions — the CREATE FUNCTION syntax, the differences from stored procedures, the three function characteristics required to avoid ERROR 1418 (DETERMINISTIC, NO SQL, READS SQL DATA), restrictions on functions, and worked examples covering pure computation, salary classification with IF/ELSEIF, table lookups with READS SQL DATA, functions in WHERE clauses, and calling functions from procedures.

A stored function is a named, parameterized block of SQL code that returns exactly one scalar value. You define it once with CREATE FUNCTION, then call it from anywhere in any expression — inside SELECT column lists, WHERE clauses, ORDER BY, even other stored programs. Use stored functions to encapsulate reusable formulas and business rules — tax calculations, format conversions, status mappings, anything you'd otherwise repeat across many queries.

The "function" name is literal: just like a function in any programming language, it takes inputs, computes, and returns one value. That distinguishes it sharply from a stored procedure, which is invoked with CALL and may return result sets but isn't usable in an expression.

The two stored-program types look similar but have fundamentally different purposes:

The required pieces:

• function_name — the name you'll use to call it. Unique per database.
• Parameter list — zero or more parameters, each with a name and a data type. No mode keyword — all parameters are implicitly IN.
• RETURNS datatype — the type of the value the function will return. Required, no exceptions.
• At least one characteristic (see below) — required when binary logging is on.
• RETURN expression — somewhere in the body, returning a value of the declared type. The function exits as soon as RETURN executes.

Function characteristics tell MySQL what kind of work the function does — does it read tables, modify them, depend only on its inputs, etc. The key ones:

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)

CREATE DATABASE IF NOT EXISTS gowtham;
USE gowtham;

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id     INT UNSIGNED PRIMARY KEY,
    first_name      VARCHAR(20),
    last_name       VARCHAR(25)    NOT NULL,
    salary          DECIMAL(10, 2) NOT NULL,
    department_id   INT
);

INSERT INTO employees VALUES
    (100, 'Steven',    'King',      24000.00,  9),
    (101, 'Neena',     'Kochhar',   17000.00,  9),
    (102, 'Lex',       'De Haan',   17000.00,  9),
    (103, 'Alexander', 'Hunold',     9000.00,  6),
    (104, 'Bruce',     'Ernst',      6000.00,  6),
    (105, 'David',     'Austin',     4800.00,  6),
    (106, 'Valli',     'Pataballa',  4800.00,  6),
    (107, 'Diana',     'Lorentz',    4200.00,  6),
    (108, 'Nancy',     'Greenberg', 12000.00, 10),
    (109, 'Daniel',    'Faviet',     9000.00, 10);

The simplest case: a function that does pure math with no database access. Convert a salary to its hourly equivalent (assuming a 2080-hour work year):

DROP FUNCTION IF EXISTS hourly_rate;

DELIMITER $$

CREATE FUNCTION hourly_rate(annual_salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN annual_salary / 2080;
END$$

DELIMITER ;

-- Use it directly in a SELECT
SELECT first_name, last_name, salary, hourly_rate(salary) AS hourly
FROM   employees
ORDER  BY salary DESC
LIMIT  5;

Notice hourly_rate(salary) appears in the SELECT column list as if it were any built-in function. This is what stored functions are for — you can't do this with a procedure.

A function returning a categorical label based on the input. The original tutorial alluded to this function but never actually wrote it — here's the full definition:

DROP FUNCTION IF EXISTS empsal_range;

DELIMITER $$

CREATE FUNCTION empsal_range(p_salary DECIMAL(10, 2))
RETURNS VARCHAR(10)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE label VARCHAR(10);

    IF p_salary >= 17000 THEN
        SET label = 'high';
    ELSEIF p_salary >= 10000 THEN
        SET label = 'avg';
    ELSE
        SET label = 'low';
    END IF;

    RETURN label;
END$$

DELIMITER ;

-- Use it on every row
SELECT first_name, last_name, salary, empsal_range(salary) AS range_label
FROM   employees
ORDER  BY salary DESC;

Now empsal_range(salary) works as a single column in any query — like a tiny view materialized per-row.

Functions can also query tables to compute their result. Look up an employee's full name by ID:

DROP FUNCTION IF EXISTS emp_full_name;

DELIMITER $$

CREATE FUNCTION emp_full_name(p_emp_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
BEGIN
    DECLARE full_name VARCHAR(50);

    SELECT CONCAT(first_name, ' ', last_name)
    INTO   full_name
    FROM   employees
    WHERE  employee_id = p_emp_id
    LIMIT  1;

    RETURN COALESCE(full_name, '(unknown)');
END$$

DELIMITER ;

SELECT emp_full_name(100) AS who_is_100,
       emp_full_name(108) AS who_is_108,
       emp_full_name(999) AS who_is_999;

The killer use case: filter rows using a stored function. Find everyone classified as "high" salary:

SELECT employee_id, first_name, last_name, salary
FROM   employees
WHERE  empsal_range(salary) = 'high'
ORDER  BY salary DESC;

The same tools that list procedures work for functions:

-- All functions in a database
SHOW FUNCTION STATUS WHERE db = 'gowtham';

-- Same thing, programmatic version
SELECT routine_name, data_type AS returns
FROM   information_schema.ROUTINES
WHERE  routine_type   = 'FUNCTION'
  AND  routine_schema = 'gowtham';

-- Full DDL
SHOW CREATE FUNCTION empsal_range\G

A procedure that uses our salary-range function:

DROP PROCEDURE IF EXISTS GetEmpDetails;

DELIMITER $$

CREATE PROCEDURE GetEmpDetails()
    READS SQL DATA
    COMMENT 'List employees with their salary range label'
BEGIN
    SELECT first_name,
           last_name,
           salary,
           empsal_range(salary) AS range_label
    FROM   employees
    ORDER  BY salary DESC;
END$$

DELIMITER ;

CALL GetEmpDetails();

Functions are also callable from triggers, events, and other functions — anywhere SQL expressions are valid.

DROP FUNCTION IF EXISTS empsal_range;

Always use IF EXISTS in deployment scripts. Note: like DROP PROCEDURE, no parentheses on the name.

Functions have several restrictions that procedures don't, because they need to behave predictably when used inside expressions:

If you have many existing functions to migrate without adding characteristics, MySQL has an escape hatch:

-- Skip the determinism check — risky for replication, use sparingly
SET GLOBAL log_bin_trust_function_creators = 1;

1. Always declare characteristics. DETERMINISTIC for pure computation, READS SQL DATA for table reads, NO SQL for math-only functions. Avoids ERROR 1418 and helps the optimizer.
2. Match RETURNS type to actual return values. Implicit conversions can mask bugs.
3. Always use DROP FUNCTION IF EXISTS at the top of deployment scripts.
4. Prefix parameter names (p_salary) to avoid shadowing column names. Inside a SELECT INTO, WHERE salary = salary is a self-comparison, not what you want.
5. Use LIMIT 1 on SELECT INTO when the predicate isn't on a unique key — prevents error 1172 if more than one row matches.
6. Handle no-match cases. A SELECT INTO with no matching rows leaves the variable NULL; use COALESCE or check explicitly.
7. Don't use functions in WHERE clauses on large tables unless you've added a functional index. The function runs per row.
8. Keep functions deterministic when possible. Deterministic functions can be cached, used in generated columns, and indexed.
9. Add a COMMENT describing what the function does and any non-obvious assumptions.

• A stored function is a named, parameterized SQL block that returns exactly one scalar value via RETURN. Use it anywhere an expression is allowed: SELECT columns, WHERE clauses, ORDER BY, even in other functions.
• Functions differ from procedures: functions return a value usable in expressions; procedures use CALL and may return result sets and OUT/INOUT parameters.
• All function parameters are IN-only — no mode keyword needed or allowed.
• Required syntax: CREATE FUNCTION name(params) RETURNS type [characteristic ...] BEGIN ... RETURN expr; END.
• Always declare a characteristic (DETERMINISTIC, NO SQL, or READS SQL DATA) — without one, you'll hit ERROR 1418 on any server with binary logging enabled.
• Restrictions: no result sets, no OUT/INOUT, no PREPARE, no transaction control, scalar return only.
• Functions in WHERE clauses can defeat indexes — for performance-critical predicates, inline the logic or use a functional index (MySQL 8.0+).