MySQL Stored Functions

A stored function in MySQL is a named block of SQL code that accepts input parameters, performs a computation, and always returns a single value. Unlike stored procedures (which are called with CALL), functions are used directly inside SQL statements like SELECT, WHERE, and INSERT.

Stored Function vs Stored Procedure

Syntax

DELIMITER //

CREATE FUNCTION function_name (parameter datatype)
RETURNS return_datatype
DETERMINISTIC
BEGIN
    -- SQL logic
    RETURN value;
END //

DELIMITER ;

DETERMINISTIC tells MySQL the function always returns the same result for the same input — required for replication and binary logging.

Example: Function to Calculate Annual Salary

DELIMITER //

CREATE FUNCTION annual_salary(monthly_sal DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN monthly_sal * 12;
END //

DELIMITER ;

Using the Function in a Query

SELECT emp_name, salary, annual_salary(salary) AS yearly_salary
FROM employees;

Result:

emp_name    | salary   | yearly_salary
------------+----------+--------------
Ravi Kumar  | 72000.00 | 864000.00
Sneha Joshi | 45000.00 | 540000.00

Example: Function to Return Grade Based on Salary

DELIMITER //

CREATE FUNCTION salary_grade(sal DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE grade VARCHAR(10);

    IF sal >= 80000 THEN
        SET grade = 'Grade A';
    ELSEIF sal >= 60000 THEN
        SET grade = 'Grade B';
    ELSEIF sal >= 40000 THEN
        SET grade = 'Grade C';
    ELSE
        SET grade = 'Grade D';
    END IF;

    RETURN grade;
END //

DELIMITER ;

SELECT emp_name, salary, salary_grade(salary) AS grade
FROM employees;

Example: Function to Concatenate Full Name

DELIMITER //

CREATE FUNCTION full_name(fname VARCHAR(50), lname VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    RETURN CONCAT(fname, ' ', lname);
END //

DELIMITER ;

SELECT full_name('Anita', 'Roy');  -- Anita Roy

Dropping a Function

DROP FUNCTION IF EXISTS annual_salary;

Viewing Functions

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';

Key Points

• A stored function always returns exactly one value using the RETURN statement.
• Functions are used inline in SQL expressions — inside SELECT, WHERE, SET, etc.
• The DETERMINISTIC keyword is required when the function always returns the same result for the same input.
• Local variables are declared with DECLARE and assigned with SET.
• Use DROP FUNCTION IF EXISTS to safely remove a function.