MySQL Stored Procedures: A Complete Beginner's Guide

MySQL

As applications grow, developers often find themselves writing the same SQL queries repeatedly. Whether it's generating reports, updating inventory, calculating salaries, or fetching customer details, repetitive SQL code can make applications difficult to maintain.

MySQL provides a powerful solution to this problem: Stored Procedures.

A stored procedure is a collection of SQL statements stored inside the database that can be executed whenever needed. Instead of sending multiple SQL queries from your application, you simply call the stored procedure.

In this guide, we'll explore everything you need to know about MySQL Stored Procedures, including how to create them, pass parameters, handle errors, and use them effectively in real-world applications.

What Is a Stored Procedure?

A Stored Procedure is a precompiled set of SQL statements saved inside the MySQL database.

Once created, it can be executed multiple times without rewriting the SQL logic.

Think of it as a reusable function for your database.

Instead of writing:

Multiple SELECT queries
Multiple UPDATE statements
Validation logic
Business rules

...every time, you can store them in one procedure and execute it with a single command.

Why Use Stored Procedures?

Stored procedures help simplify database operations and improve code reusability.

Benefits include:

Reusable SQL logic
Reduced network traffic
Better performance
Improved security
Easier maintenance
Centralized business logic
Consistent data operations

Instead of embedding complex SQL inside your application, you can move that logic into the database.

Creating Your First Stored Procedure

Suppose you want to retrieve all active users.

Create Procedure

DELIMITER //

CREATE PROCEDURE GetActiveUsers()
BEGIN
    SELECT *
    FROM users
    WHERE status = 'Active';
END //

DELIMITER ;

Execute the Procedure

CALL GetActiveUsers();

Instead of writing the SELECT query repeatedly, you simply call the procedure.

Stored Procedures with Input Parameters

Stored procedures become much more useful when they accept parameters.

Suppose you want to retrieve a user by ID.

Create Procedure

DELIMITER //

CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT *
    FROM users
    WHERE id = userId;
END //

DELIMITER ;

Execute

CALL GetUserById(5);

The procedure returns the user whose ID is 5.

Why Use Parameters?

Parameters allow one procedure to handle many different inputs without creating separate queries.

Understanding Parameter Types

MySQL supports three parameter modes.

IN Parameter

Used to pass values into a procedure.

Example:

CALL GetUserById(10);

The value is available only inside the procedure.

OUT Parameter

Returns a value from the procedure.

Example:

DELIMITER //

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

DELIMITER ;

Execute:

CALL GetTotalUsers(@count);

SELECT @count;

The total number of users is stored in @count.

INOUT Parameter

An INOUT parameter acts as both an input and an output.

Example:

DELIMITER //

CREATE PROCEDURE IncreaseValue(INOUT number INT)
BEGIN
    SET number = number + 10;
END //

DELIMITER ;

Execute:

SET @value = 20;

CALL IncreaseValue(@value);

SELECT @value;

Result:

Using Variables Inside Procedures

Stored procedures can declare local variables.

Example:

DELIMITER //

CREATE PROCEDURE Example()
BEGIN
    DECLARE totalUsers INT;

    SELECT COUNT(*)
    INTO totalUsers
    FROM users;

    SELECT totalUsers;
END //

DELIMITER ;

Variables are useful for calculations and temporary values.

Conditional Logic with IF

Stored procedures support conditional statements.

Example:

IF total > 100 THEN
    SELECT 'Premium Customer';
ELSE
    SELECT 'Regular Customer';
END IF;

Common Use Cases

User verification
Discount calculation
Salary processing
Role-based logic

Using CASE Statements

When multiple conditions exist, CASE improves readability.

Example:

CASE
    WHEN marks >= 90 THEN 'Grade A'
    WHEN marks >= 75 THEN 'Grade B'
    ELSE 'Grade C'
END;

CASE is cleaner than writing many nested IF statements.

Looping Inside Stored Procedures

MySQL supports loops.

Common loop types include:

LOOP

Runs continuously until explicitly exited.

WHILE

Executes while a condition is true.

Example:

WHILE counter <= 10 DO
    SET counter = counter + 1;
END WHILE;

REPEAT

Executes at least once before checking the condition.

Useful for batch operations.

Updating Data with Stored Procedures

Stored procedures aren't limited to SELECT queries.

Example:

DELIMITER //

CREATE PROCEDURE UpdateSalary(
    IN employeeId INT,
    IN newSalary DECIMAL(10,2)
)
BEGIN
    UPDATE employees
    SET salary = newSalary
    WHERE id = employeeId;
END //

DELIMITER ;

Execute:

CALL UpdateSalary(3,65000);

Using Transactions Inside Stored Procedures

Stored procedures can execute transactions.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

If something fails:

ROLLBACK;

This ensures both operations succeed together.

Error Handling

Stored procedures support exception handling.

Example:

DECLARE EXIT HANDLER
FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
END;

This automatically rolls back the transaction if an SQL error occurs.

Why Is Error Handling Important?

Without proper error handling:

Partial updates may occur.
Data inconsistencies can appear.
Transactions may remain incomplete.

Real-World Examples

Banking Application

A stored procedure can:

Validate accounts
Transfer funds
Record transactions
Commit changes

All inside a single procedure.

E-Commerce Website

One procedure can:

Create an order
Reduce inventory
Calculate tax
Generate an invoice
Update loyalty points

HR Management System

Stored procedures can:

Calculate monthly salaries
Update attendance
Generate payslips
Compute bonuses

Student Management System

A procedure can:

Insert marks
Calculate grades
Update GPA
Generate report cards

Advantages of Stored Procedures

Faster execution because SQL is precompiled.
Reduces duplicate SQL code.
Simplifies application development.
Improves database security.
Centralizes business logic.
Easier maintenance.
Reduces network traffic.
Encourages code reuse.

Disadvantages of Stored Procedures

More difficult to debug than application code.
Database-specific syntax reduces portability.
Large procedures can become difficult to maintain.
Business logic spread between application and database may confuse developers.

Use stored procedures where they truly add value.

Best Practices

Keep Procedures Small

Each procedure should perform one specific task.

Use Meaningful Names

Good examples:

GetEmployeeDetails
CreateInvoice
UpdateInventory
CalculateSalary

Avoid generic names like TestProcedure.

Handle Errors Properly

Always include exception handling when modifying data.

Use Transactions for Critical Operations

Financial and inventory operations should always use transactions.

Document Your Procedures

Add comments explaining:

Purpose
Parameters
Expected output
Business rules

This makes maintenance easier.

When Should You Use Stored Procedures?

Stored procedures are ideal for:

Financial transactions
Inventory updates
Report generation
Payroll processing
Bulk data imports
Complex validations
Frequently executed SQL operations
Administrative tasks

Common Mistakes

Creating extremely large procedures.
Ignoring error handling.
Mixing unrelated business logic in one procedure.
Not using transactions for critical updates.
Using procedures for every simple query unnecessarily.
Poor naming conventions.

Final Thoughts

Stored Procedures are a powerful feature of MySQL that help developers write cleaner, reusable, and more efficient database code. By moving frequently used SQL logic into the database, they reduce duplication, simplify application development, and improve consistency across projects.

However, stored procedures should be used thoughtfully. They are most effective for complex operations such as financial transactions, report generation, inventory management, and business rule enforcement. Simple CRUD operations often don't require a stored procedure and may be easier to manage directly from the application.

By combining stored procedures with proper transactions, error handling, and meaningful naming conventions, you can build scalable, secure, and maintainable database applications that perform well even as your data and user base continue to grow.