MySQL Stored Functions

A stored function is a user-defined function stored in the database that accepts parameters, performs a calculation or lookup, and returns a single value. Unlike a stored procedure, a function can be called inline anywhere an expression is valid — inside a SELECT list, a WHERE clause, or as a default value.

Syntax

DELIMITER $$

CREATE FUNCTION function_name(param1 type, param2 type, ...)
RETURNS return_type
DETERMINISTIC
BEGIN
    DECLARE result return_type;
    -- logic here
    RETURN result;
END$$

DELIMITER ;

• RETURNS (with an S) declares the data type the function will return.
• RETURN (no S) is the statement that actually sends the value back.
• DETERMINISTIC tells MySQL the function always returns the same output for the same input — required (or NOT DETERMINISTIC) for binary logging.

Example — Calculate Discounted Price

DELIMITER $$

CREATE FUNCTION DiscountedPrice(p_price DECIMAL(10,2), p_pct INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_price - (p_price * p_pct / 100);
END$$

DELIMITER ;

Call it directly inside a SELECT:

SELECT name, price, DiscountedPrice(price, 15) AS sale_price
FROM products;

Example — Full Name Formatter

DELIMITER $$

CREATE FUNCTION FullName(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    RETURN CONCAT(p_first, ' ', p_last);
END$$

DELIMITER ;

SELECT FullName('Rahim', 'Uddin');   -- 'Rahim Uddin'

Functions vs Stored Procedures

DROP FUNCTION

DROP FUNCTION IF EXISTS DiscountedPrice;

Key Points to Remember

• A stored function always returns exactly one value via the RETURN statement.
• Declare the return type with RETURNS type in the function header.
• Mark the function DETERMINISTIC if it always returns the same value for the same inputs.
• Call a function inline — in SELECT, WHERE, or any expression — unlike a procedure which needs CALL.
• Use functions for computed values and formatting; use procedures for multi-step operations.