Stored Procedures: Organization and Code Quality in SQL

Among the advanced features of SQL, stored procedures are one of my favorite tools when it comes to keeping systems clean, organized, and efficient in relational databases.

The examples in this article will all be in MySQL, but keep in mind that stored procedures are also supported in MariaDB, PostgreSQL, SQL Server, and Oracle.

Advantages

Security

You can grant execution-only permissions on a procedure to certain users, without giving them direct access to insert or modify data in the tables. On top of that, procedures enforce fixed rules, minimizing the risk of human error.

Performance

Using stored procedures improves performance since, instead of executing multiple SQL statements from application code (opening and closing connections repeatedly), you can store the logic directly in the database. This makes the system faster because all operations run in sequence inside SQL itself.

Encapsulation

Stored procedures allow you to encapsulate business rules and hide implementation details. This makes your system cleaner and easier for developers to read and maintain.

Reusability

In a microservices architecture, one common challenge is duplicating the same business logic across multiple services. Whenever that logic changes, you have to refactor it everywhere. With stored procedures, you just update the SQL once and call it from all services, simplifying maintenance.

How to create a procedure

DELIMITER $$

CREATE PROCEDURE verify_avaiable_suppliers(
    IN p_avaiable_budget DECIMAL(10, 2),
    IN p_product_id INT
)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Products WHERE id = p_product_id) THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Product does not exist';
    ELSE
        SELECT *
        FROM Suppliers s
        WHERE s.product_id = p_product_id
          AND s.price <= p_avaiable_budget
        ORDER BY s.price;
    END IF;
END $$

DELIMITER ;

Inside a procedure, you can declare variables and build more advanced logic with WHILE loops, IF conditions, and variable declarations, enabling you to implement sophisticated workflows directly in SQL.

How to use it

To execute a procedure, just call it as if it were a regular function:

CALL verify_avaiable_suppliers();

How to modify a procedure

Unfortunately, MySQL does not allow direct edits to stored procedures. You need to drop and recreate them.
The execution time is usually short, but it can cause temporary instability in the database.

DROP PROCEDURE IF EXISTS verify_avaiable_suppliers;

How to debug a procedure

Personally, I debug complex procedures by adding SELECT statements as if they were “breakpoints.” This helps identify exactly where the logic isn’t working as expected.

DELIMITER $$

CREATE PROCEDURE verify_avaiable_suppliers(
    IN p_avaiable_budget DECIMAL(10, 2),
    IN p_product_id INT
)
BEGIN
    SELECT p_avaiable_budget;  -- Debug breakpoint
END $$

DELIMITER ;

Things to watch out for

Complexity

Very large procedures (800+ lines) can be confusing and difficult for new team members to maintain.

Permissions

Must be carefully managed, especially for critical procedures that should only be executed by authorized roles.

Team knowledge

If most developers on your team have only basic SQL knowledge, heavy reliance on procedures can create risks.

System flexibility

Some business rules might become “locked” inside a procedure, making it harder to test or adapt.

Procedures calling other procedures

This can overcomplicate logic and make the code harder to follow and maintain.