Functions (User Defined) — Create Your Own Reusable SQL Logic

Several reports classify marks with the same threshold. A named function can express that shared database-side value rule.

A stored SQL function returns a value that can be used in expressions. Its parameter and result contract is different from a procedure called for a multi-statement operation.

A MySQL stored function declares its input types and RETURNS type, then computes and returns a value. A deterministic calculation produces the same result for the same inputs, but routine creation privileges and binary-logging policies can still affect whether creation is permitted.

Related concepts: Choose Values with CASE and Stored Procedures.

A User Defined Function is a named SQL routine that accepts parameters, performs some calculation, and returns a value. It’s like a mini-program you can plug into any SQL query.

MySQL 8.4 · stored function

DELIMITER $$

CREATE FUNCTION function_name(parameters)
RETURNS datatype
DETERMINISTIC
BEGIN
  -- function logic
  RETURN value;
END$$

DELIMITER ;

MySQL 8.4 · stored function

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  dob DATE,
  marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '2008-06-12', 85),
(2, 'Diya Iyer', '2009-11-23', 92),
(3, 'Sneha Patil', '2007-05-17', 78),
(4, 'Mehul Agarwal', '2008-01-03', 64);

Correction to the retained example: this function uses CURDATE(), so its answer changes with the current date. Declaring it DETERMINISTIC is misleading. The displayed ages are historical illustrative values, not results guaranteed today. Use an explicit as-of-date parameter when you need a repeatable age calculation; the corrected version follows immediately below.

MySQL 8.4 · stored function

DELIMITER $$

CREATE FUNCTION calculate_age(dob DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, dob, CURDATE());
END$$

DELIMITER ;

Usage:

SELECT name, calculate_age(dob) AS age
FROM students;

name           | age
----------------+-----
Aarav Sharma   | 16
Diya Iyer      | 14
Sneha Patil    | 17
...

Use a stated assessment date · MySQL 8.4

DELIMITER //
CREATE FUNCTION calculate_age_on(dob DATE, as_of_date DATE)
RETURNS INT DETERMINISTIC NO SQL
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, dob, as_of_date);
END //
DELIMITER ;
SELECT name, calculate_age_on(dob, '2024-10-07') AS age
FROM students ORDER BY roll_no;

On 2024-10-07, the four sample students are 16, 14, 17 and 16 years old. The same birth date and assessment date give the same answer. The original CURDATE-based version should instead be declared NOT DETERMINISTIC; this parameterized version makes the report date explicit.

MySQL 8.4 · stored function

DELIMITER $$

CREATE FUNCTION get_grade(score INT)
RETURNS CHAR(2)
DETERMINISTIC
BEGIN
  DECLARE grade CHAR(2);
  
  IF score >= 90 THEN
    SET grade = 'A+';
  ELSEIF score >= 75 THEN
    SET grade = 'A';
  ELSEIF score >= 60 THEN
    SET grade = 'B';
  ELSE
    SET grade = 'C';
  END IF;
  
  RETURN grade;
END$$

DELIMITER ;

Usage:

SELECT name, marks, get_grade(marks) AS grade
FROM students;

name           | marks | grade
----------------+--------+-------
Aarav Sharma   | 85     | A
Diya Iyer      | 92     | A+
Sneha Patil    | 78     | A
Mehul Agarwal  | 64     | B

MySQL 8.4 · stored function

DROP FUNCTION IF EXISTS get_grade;

• calculate_age – for attendance sheets, eligibility checks
• get_grade – for generating report cards
• Custom get_percentage() or get_status() functions for result dashboards

• Mark functions as DETERMINISTIC if the same input always gives the same result (improves performance).
• Keep functions focused — one task per function.
• Test thoroughly — especially when using inside SELECTs or reports.

A function that queries changing data is not automatically deterministic. Stored-function restrictions differ from procedure restrictions. A simple CASE expression may be easier to maintain than a new function when the rule is used only once.

Define a deterministic MySQL function for the mark status and use it in a report.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

MySQL 8.4 · stored function

CREATE FUNCTION practice_status(mark_value INT) RETURNS VARCHAR(20) DETERMINISTIC NO SQL RETURN CASE WHEN mark_value >= 80 THEN 'Strong' ELSE 'Developing' END;
SELECT name, practice_status(marks) AS status FROM practice_students ORDER BY roll_no;

Each row supplies its mark as the argument and receives one status value. The function does not update the stored marks.

Run this engine-specific script on a fresh MySQL 8.4 practice schema with the required routine privileges. It is not executable in SQLite.

A stored SQL function returns a value that can be used in expressions. Its parameter and result contract is different from a procedure called for a multi-statement operation.

Each row supplies its mark as the argument and receives one status value. The function does not update the stored marks.

Continue your understanding with Choose Values with CASE and Stored Procedures.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.