MySQL Stored Functions – Define, Use, and Reuse Computed Logic

Introduction – Why Use Stored Functions in MySQL?

Stored functions in MySQL are routines that return a single value and can be used within SQL statements just like built-in functions (NOW(), CONCAT(), etc.). They allow you to:

• Encapsulate reusable calculations
• Simplify queries
• Centralize business logic
• Improve code readability and maintainability

In this guide, you’ll learn:

• How to create and use stored functions
• Syntax and return types
• Best practices and use cases
• How to manage and remove functions

1. What Is a Stored Function?

A stored function is a named block of SQL code that:

• Accepts parameters (IN)
• Returns a single scalar value
• Can be used in SELECT, WHERE, ORDER BY, and other clauses

2. Syntax – CREATE FUNCTION

DELIMITER //

CREATE FUNCTION function_name(param_name datatype)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
  -- Logic
  RETURN value;
END //

DELIMITER ;

3. Example – Simple Discount Calculator

DELIMITER //

CREATE FUNCTION get_discount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.90;
END //

DELIMITER ;

Usage in a Query

SELECT product_name, price, get_discount(price) AS discounted_price
FROM products;

Computes 10% discount for every product on the fly.

4. Parameter Rules

• Only IN parameters are allowed
• You must declare the return type (RETURNS)
• Use RETURN to return a value
• Always end with DELIMITER ;

5. Example – Days Between Dates

DELIMITER //

CREATE FUNCTION days_between(start_date DATE, end_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN DATEDIFF(end_date, start_date);
END //

DELIMITER ;

Example Usage

SELECT days_between(order_date, delivery_date) AS days_waited
FROM orders;

6. View & Drop Functions

View All Stored Functions

SHOW FUNCTION STATUS WHERE Db = 'your_database';

Drop Function

DROP FUNCTION IF EXISTS get_discount;

Stored Function vs Stored Procedure

Best Practices

Real-World Use Cases

Summary – Recap & Next Steps

MySQL stored functions allow you to embed reusable, calculated logic into your SQL queries. They are perfect for centralizing formulas, custom expressions, and on-the-fly transformations.

Key Takeaways

• Stored functions return a single value
• Use them inside SQL statements (SELECT, WHERE, etc.)
• Must declare input parameters and return type
• Use DETERMINISTIC where logic is consistent
• Avoid side effects (no INSERT, UPDATE, etc.)

Real-World Relevance

Stored functions are ideal for e-commerce discounts, analytics formulas, data formatting, and lookup optimizations in production databases.

FAQ – MySQL Stored Functions

Can I use INSERT, UPDATE, or DELETE inside a function?

No. Functions must be read-only. Use procedures for data changes.

Can I use a function in a WHERE clause?

Yes. For example:

SELECT * FROM orders WHERE days_between(order_date, NOW()) > 30;

What’s the difference between DETERMINISTIC and NOT DETERMINISTIC?

• DETERMINISTIC: Always returns the same result for the same inputs
• NOT DETERMINISTIC: May vary due to randomness, time, or data changes

How many functions can I create in MySQL?

There is no hard limit; limited only by database performance and organization.

Can I pass multiple arguments to a function?

Yes, declare as many IN parameters as needed:

CREATE FUNCTION calc_total(p DECIMAL, tax DECIMAL) RETURNS DECIMAL ...