Mr. Editor-in-chief Mr. Editor-in-chief • September 6, 2025 • Updated April 24, 2026

Advanced MySQL Topics - Functions vs Stored Procedures

A summary of the key differences between stored procedures and stored functions

Functions Procedures
Created using CREATE FUNCTION command Created using the CREATE PROCEDURE command
Invoked using the SELECT statement Invoked using the CALL statement
Must return a single value Outputs values via the OUT parameter
Takes IN parameters only Takes IN, OUT and INOUT parameters
Typically encapsulates common formulas or generic business rules Typically used to process, manipulate and modify data in the database
Must specify the data type of the return value User must specify the OUT parameter type

Database Preparation

CREATE DATABASE Lucky_Shrub;
USE Lucky_Shrub;
CREATE TABLE Clients (
    ClientID VARCHAR(5) NOT NULL PRIMARY KEY,
    FullName VARCHAR(50),
    ContactNumber VARCHAR(10),
    AddressID INT
    );
CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    ClientID VARCHAR(10),
    ProductID VARCHAR(10),
    Quantity INT,
    Cost DECIMAL(6,2),
    Date DATE
    ); 
INSERT INTO Clients
  (ClientID, FullName, ContactNumber, AddressID)
  VALUES
  ('Cl1', 'Takashi Ito', '351786345', 1),
  ('Cl2', 'Jane Murphy', '351567243', 2),
  ('Cl3', 'Laurina Deldago', '351342597', 3),
  ('Cl4', 'Benjamin Clauss', '351342509', 4),
  ('Cl5', 'Altay Ayhan', '351208983', 5),
  ('Cl6', 'Greta Galkina', '351298755', 6);
INSERT INTO Orders(OrderID, ClientID, ProductID , Quantity, Cost, Date) VALUES
(1, "Cl1", "P1", 10, 500, "2020-09-01"),
(2, "Cl2", "P2", 5, 100, "2020-09-05"),
(3, "Cl3", "P3", 20, 800, "2020-09-03"),
(4, "Cl4", "P4", 15, 150, "2020-09-07"),
(5, "Cl3", "P3", 10, 450, "2020-09-08"),
(6, "Cl2", "P2", 5, 800, "2020-09-09"),
(7, "Cl1", "P4", 22, 1200, "2020-09-10"),
(8, "Cl3", "P1", 15, 150, "2020-09-10"),
(9, "Cl1", "P1", 10, 500, "2020-09-12"),
(10, "Cl2", "P2", 5, 100, "2020-09-13"),
(11, "Cl4", "P5", 5, 100, "2020-09-15"),
(12, "Cl1", "P1", 10, 500, "2022-09-01"),
(13, "Cl2", "P2", 5, 100, "2022-09-05"),
(14, "Cl3", "P3", 20, 800, "2022-09-03"),
(15, "Cl4", "P4", 15, 150, "2022-09-07"),
(16, "Cl3", "P3", 10, 450, "2022-09-08"),
(17, "Cl2", "P2", 5, 800, "2022-09-09"),
(18, "Cl1", "P4", 22, 1200, "2022-09-10"),
(19, "Cl3", "P1", 15, 150, "2022-09-10"),
(20, "Cl1", "P1", 10, 500, "2022-09-12"),
(21, "Cl2", "P2", 5, 100, "2022-09-13"),
(22, "Cl2", "P1", 10, 500, "2021-09-01"),
(23, "Cl2", "P2", 5, 100, "2021-09-05"),
(24, "Cl3", "P3", 20, 800, "2021-09-03"),
(25, "Cl4", "P4", 15, 150, "2021-09-07"),
(26, "Cl1", "P3", 10, 450, "2021-09-08"),
(27, "Cl2", "P1", 20, 1000, "2022-09-01"),
(28, "Cl2", "P2", 10, 200, "2022-09-05"),
(29, "Cl3", "P3", 20, 800, "2021-09-03"),
(30, "Cl1", "P1", 10, 500, "2022-09-01");

Simple function and stored procedure Examples

-- AVG is a MySQL built-in function
SELECT AVG(Cost) FROM Orders;
-- We can create a user defined function
CREATE FUNCTION IF NOT EXISTS GetCostAverage() RETURNS DECIMAL(5,2) DETERMINISTIC
RETURN (SELECT AVG(Cost) FROM Orders);
-- Invoke(call) the newly created function
SELECT GetCostAverage();

-- Create a stored procedure
CREATE PROCEDURE IF NOT EXISTS GetAllClients()
SELECT * FROM Clients;
-- invoke(call) the newly created stored procedure
SELECT GetCostAverage();

-- Create a SQL function that prints the cost value of a specific order based on the user input of the OrderID. The expected output result should be the same as the result
CREATE FUNCTION IF NOT EXISTS FindCost(orderId INT) RETURNS INT DETERMINISTIC
RETURN (SELECT Cost from Orders WHERE OrderID = orderId);
-- The above is wrong because orderId and OrderID are the same.  SQL patterns are case-insensitive by default.
CREATE FUNCTION FindCost(order_id INT)
RETURNS DECIMAL (6,2) DETERMINISTIC
RETURN (SELECT Cost FROM Orders WHERE OrderID = order_id);

SELECT FindCost(30);

-- Create a stored procedure called GetDiscount()
DELIMITER //
CREATE Procedure GetDiscount(order_id INT)
  BEGIN
      DECLARE cost_after_discount DECIMAL(7,2);
      DECLARE current_cost DECIMAL(7,2);
      DECLARE order_quantity INT;
      SELECT Quantity INTO order_quantity FROM Orders WHERE OrderID = order_id;
      SELECT Cost INTO current_cost FROM Orders WHERE OrderID = order_id;
     IF order_quantity >= 20 THEN
       SET cost_after_discount = current_cost - (current_cost * 0.2);
     ELSEIF order_quantity >= 10 THEN
       SET cost_after_discount = current_cost - (current_cost * 0.1);
     ELSE SET cost_after_discount = current_cost;
     END IF;
     SELECT cost_after_discount;
    END//
DELIMITER ;

CALL GetDiscount(5);

-- List all user defined functions and procedures
SHOW FUNCTION STATUS WHERE Db != 'sys';
-- List all user defined procedures
SHOW PROCEDURE STATUS WHERE Db != 'sys';
-- Check the details of a stored procedure
SHOW CREATE PROCEDURE Lucky_Shrub.GetAllClients;
-- DROP FUNCTION 
DROP FUNCTION FindCost;
DROP FUNCTION GetCostAverage;
-- DROP PROCEDURE
DROP PROCEDURE GetAllClients;
DROP PROCEDURE GetDiscount;
← Back