• 21 Mar, 2026
• 0 Comments
• 2 Mins Read

Stored Procedures in MySQL

Stored Procedures in MySQL – Complete Guide

Stored Procedures are one of the most powerful features in MySQL that help developers and database administrators write reusable, efficient, and secure database logic. Instead of writing the same SQL queries multiple times, you can encapsulate them into a stored procedure and call it whenever needed.

In this complete guide, we’ll explore everything you need to know about Stored Procedures in MySQL, from basics to advanced usage.

What is a Stored Procedure?

A Stored Procedure is a precompiled collection of SQL statements stored inside the database. It can be executed whenever required by calling its name.

Key Benefits:

• Code reusability

• Improved performance

• Reduced network traffic

• Better security and control

• Easier maintenance

Basic Syntax of Stored Procedure

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
-- SQL statements
END //

DELIMITER ;

Example: Simple Stored Procedure

DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM employees;
END //

DELIMITER ;

Calling the Procedure:

CALL GetAllEmployees();

Stored Procedure with Parameters

Stored procedures can accept parameters to make them dynamic.

Types of Parameters:

• IN (default) – Input parameter

• OUT – Output parameter

• INOUT – Both input and output

Example with IN Parameter

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
SELECT * FROM employees WHERE id = emp_id;
END //

DELIMITER ;

Example with OUT Parameter

DELIMITER //

CREATE PROCEDURE GetEmployeeCount(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM employees;
END //

DELIMITER ;

CALL GetEmployeeCount(@count);
SELECT @count;

DELIMITER //
CREATE PROCEDURE IncreaseSalary(INOUT sal DECIMAL(10,2))
BEGIN
SET sal = sal + 1000;
END //

DELIMITER ;

SET @salary = 5000;
CALL IncreaseSalary(@salary);
SELECT @salary;

Control Flow in Stored Procedures

MySQL allows control flow statements similar to programming languages.

IF Statement Example

DELIMITER //

CREATE PROCEDURE CheckSalary(IN sal INT)
BEGIN
IF sal > 50000 THEN
SELECT 'High Salary';
ELSE
SELECT 'Low Salary';
END IF;
END //

DELIMITER ;

LOOP Example

DELIMITER //

CREATE PROCEDURE LoopExample()
BEGIN
DECLARE i INT DEFAULT 1;

simple_loop: LOOP
IF i > 5 THEN
LEAVE simple_loop;
END IF;

SELECT i;
SET i = i + 1;
END LOOP;
END //

DELIMITER ;

Advantages of Stored Procedures

• Faster execution due to precompilation

• Reduces application code complexity

• Centralized business logic

• Enhanced security using permissions

• Minimizes SQL injection risks

Disadvantages of Stored Procedures

• Harder to debug compared to application code

• Increased load on database server

• Version control can be challenging

• Limited portability across databases

Best Practices

• Use meaningful procedure names

• Keep procedures modular and simple

• Avoid overly complex logic

• Properly handle errors and exceptions

• Document your procedures clearly

Real-World Use Cases

• Data validation before insert/update

• Automating repetitive database tasks

• Generating reports

• Managing transactions

• Data transformation and migration

Conclusion

Stored Procedures in MySQL are a powerful way to manage and optimize database operations. By using them effectively, developers can improve performance, maintain cleaner code, and ensure better data security.

If you’re working in database development or aiming for roles like Data Engineer, Backend Developer, or DBA, mastering stored procedures is a must-have skill.

At Learnomate Technologies, we focus on practical, real-time training to help students master database technologies like MySQL, Oracle, and Data Engineering tools. Our hands-on approach ensures you gain industry-ready skills with real-world examples and expert guidance.