MySQL: A Detailed Explanation of MySQL Stored Procedures…
Last updated: 2026-08-26
Stored procedures are precompiled sets of SQL statements—write them once, call them many times.
This lesson covers the creation and use of stored procedures and functions.
graph TB
A[CALL Stored Procedures] --> B[Parameter Passing IN/OUT/INOUT]
B --> C[DECLARE Declare a variable]
C --> D{Process Control}
D --> E[IF / CASE Conditions]
D --> F[WHILE / LOOP Loop]
D --> G[Cursor CURSOR Iterate]
E --> H[ExecuteSQL]
F --> H
G --> H
H --> I[OUTParameter Return]
I --> J[End]
1. What You'll Learn
- CREATE PROCEDURE: Create a stored procedure
- IN/OUT/INOUT Parameter Types
- DECLARE: Variable Declaration
- IF/CASE/WHILE Flow Control
- Cursor CURSOR
2. Real-Life Scenarios
(1) Pain Point: Repeatedly Writing Complex Logic
We calculate the total payroll for each department every month, but the SQL query is very long and runs repeatedly.
(2) Solution Using Stored Procedures
SQL
CREATE PROCEDURE sp_dept_salary_stats()
BEGIN
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
END;
-- Call
CALL sp_dept_salary_stats();
3. Creating a Stored Procedure
▶ Example: Basic Stored Procedure
SQL
-- Create a Stored Procedure
DELIMITER //
CREATE PROCEDURE sp_get_user(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
-- Call
CALL sp_get_user(1);
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
+--------+
| @total |
+--------+
| value |
+--------+
1 row in set
▶ Example: With an OUT parameter
SQL
DELIMITER //
CREATE PROCEDURE sp_count_users(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;
-- Call
CALL sp_count_users(@total);
SELECT @total;
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
+-------+
| @num |
+-------+
| value |
+-------+
1 row in set
▶ Example: With INOUT parameters
SQL
DELIMITER //
CREATE PROCEDURE sp_double(INOUT num INT)
BEGIN
SET num = num * 2;
END //
DELIMITER ;
-- Call
SET @num = 5;
CALL sp_double(@num);
SELECT @num; -- 10
Output:
TEXT
📖 Display only
Output displayed
4. Variable Declaration
▶ Example: DECLARE variable
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
+----------------------+
| COUNT(*) INTO total) |
+----------------------+
| 5 |
+----------------------+
1 row in set
+------------------------------+
| AVG(salary) INTO avg_salary) |
+------------------------------+
| 30.00 |
+------------------------------+
1 row in set
+-------+------------+
| total | avg_salary |
+-------+------------+
| value | 30.00 |
+-------+------------+
1 row in set
Query OK, 0 rows affected
SQL
DELIMITER //
CREATE PROCEDURE sp_variable_demo()
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE avg_salary DECIMAL(10,2);
DECLARE user_name VARCHAR(50);
-- Assignment
SELECT COUNT(*) INTO total FROM employees;
SELECT AVG(salary) INTO avg_salary FROM employees;
-- Using Variables
SELECT total, avg_salary;
END //
DELIMITER ;
Output:
TEXT
📖 Display only
Output displayed
5. Process Control
▶ Example: IF Statement
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
----------------------
salary INTO emp_salary
----------------------
25.00
50.00
----------------------
2 rows in set
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
DELIMITER //
CREATE PROCEDURE sp_check_salary(IN emp_id INT)
BEGIN
DECLARE emp_salary DECIMAL(10,2);
SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
IF emp_salary > 10000 THEN
SELECT 'High salary' AS level;
ELSEIF emp_salary > 5000 THEN
SELECT 'Medium salary' AS level;
ELSE
SELECT 'Low salary' AS level;
END IF;
END //
DELIMITER ;
Output:
TEXT
📖 Display only
Output displayed
▶ Example: CASE Statement
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
------------------------
status INTO order_status
------------------------
active
pending
------------------------
2 rows in set
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
DELIMITER //
CREATE PROCEDURE sp_order_status(IN order_id INT)
BEGIN
DECLARE order_status VARCHAR(20);
SELECT status INTO order_status FROM orders WHERE id = order_id;
CASE order_status
WHEN 'pending' THEN SELECT 'Order is pending' AS message;
WHEN 'paid' THEN SELECT 'Order is paid' AS message;
WHEN 'shipped' THEN SELECT 'Order is shipped' AS message;
ELSE SELECT 'Unknown status' AS message;
END CASE;
END //
DELIMITER ;
Output:
TEXT
📖 Display only
Output displayed
6. Cursor
7. Stored Functions
8. Managing Stored Procedures
❓ FAQ
Q What is the difference between a stored procedure and a function?
A A stored procedure is called using the CALL statement and does not return a value; a function is called using the SELECT statement and must return a value.
Q What is DELIMITER?
A It is the statement terminator. Because the stored procedure contains
;, it needs to be replaced with // to prevent the statement from ending prematurely.Q Can stored procedures improve performance?
A Stored procedures are precompiled, which reduces network traffic. However, the modern MySQL optimizer is already quite good, so the performance gain is limited.
Q What is the difference between a stored procedure and a function?
A A procedure is called using the CALL statement and may have OUT parameters without returning a value, while a function used in a SELECT statement must RETURN a value.
Q Can stored procedures be vulnerable to SQL injection?
A Using PREPARE combined with CONCAT to construct SQL statements can lead to injection; passing parameters directly is secure.
📖 Summary
- Stored procedures are created using
CREATE PROCEDUREand called usingCALL - Parameter Type: IN (Input), OUT (Output), INOUT (Bidirectional)
- DECLARE declares a variable; SET assigns a value
- Flow control: IF/CASE/WHILE/LOOP
- Cursors are used to iterate through the result set row by row
- Stored functions must return a value
📝 Exercises
-
Basic Question (Difficulty: ⭐): Create a stored procedure that takes a user ID as input and returns the user's information.
-
Advanced Problem (Difficulty ⭐⭐): Create a stored procedure with an IF statement that returns a grade based on the score.
-
Challenge Problem (Difficulty: ⭐⭐⭐): Use a cursor to iterate through the user table, calculate and update each user's points.
