How to Create Stored Procedures in MySQL Database

The first stored procedure I ever wrote was born out of frustration, not elegance. I had a five-step monthly billing calculation that three different internal tools all needed to run, and I was tired of that logic drifting slightly out of sync between a Python script, a PHP admin panel, and a scheduled cron job. Moving that logic into a single stored procedure meant every caller, regardless of language, got exactly the same behavior. That’s really the core value proposition of stored procedures, and I want to walk through everything else I’ve learned about them since.

What a Stored Procedure Is

A stored procedure is a named, precompiled block of SQL code stored inside the database itself, which any client — an application, another procedure, a scheduled event, or a direct SQL session — can invoke with a simple CALL statement. Procedures can accept input parameters, return output parameters, and contain full procedural logic: variables, conditionals, loops, and error handling.

Why Use Stored Procedures

• Consistency: business logic lives in one place, guaranteeing every caller behaves identically.
• Performance: procedures are parsed and their execution plan cached, reducing repeated parsing overhead for frequently-run logic, and they reduce network round-trips by bundling multiple statements into one call.
• Security: you can grant EXECUTE privilege on a procedure without granting direct table access, letting an account run controlled logic without broad SELECT/UPDATE rights on the underlying tables.
• Encapsulation: complex multi-step operations become a single, simple call from the application’s perspective.

Basic Syntax

DELIMITER $$

CREATE PROCEDURE procedure_name(IN param1 datatype, OUT param2 datatype)
BEGIN
    -- procedure body
END$$

DELIMITER ;

Example 1: A Simple Procedure with an IN Parameter

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

INSERT INTO customers (name, city) VALUES
('Ayesha', 'Lahore'), ('Bilal', 'Karachi'), ('Sara', 'Lahore');

DELIMITER $$

CREATE PROCEDURE GetCustomersByCity(IN p_city VARCHAR(50))
BEGIN
    SELECT customer_id, name, city
    FROM customers
    WHERE city = p_city;
END$$

DELIMITER ;

Calling it:

CALL GetCustomersByCity('Lahore');

Output:

+-------------+--------+--------+
| customer_id | name   | city   |
+-------------+--------+--------+
|           1 | Ayesha | Lahore |
|           3 | Sara   | Lahore |
+-------------+--------+--------+

Example 2: OUT Parameters

DELIMITER $$

CREATE PROCEDURE GetCustomerCount(IN p_city VARCHAR(50), OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM customers
    WHERE city = p_city;
END$$

DELIMITER ;

CALL GetCustomerCount('Lahore', @total);
SELECT @total AS customer_count;

Output:

+----------------+
| customer_count |
+----------------+
|              2 |
+----------------+

OUT parameters let a procedure hand a computed value back to the caller through a session variable — I use this pattern constantly when a procedure needs to report a status code or a computed result alongside performing some action.

Example 3: Variables, Conditionals, and Control Flow

DELIMITER $$

CREATE PROCEDURE ApplyDiscount(IN p_customer_id INT, IN p_order_total DECIMAL(10,2), OUT p_discounted_total DECIMAL(10,2))
BEGIN
    DECLARE v_is_vip BOOLEAN DEFAULT FALSE;

    SELECT is_vip INTO v_is_vip FROM customers WHERE customer_id = p_customer_id;

    IF v_is_vip THEN
        SET p_discounted_total = p_order_total * 0.85;
    ELSEIF p_order_total > 1000 THEN
        SET p_discounted_total = p_order_total * 0.95;
    ELSE
        SET p_discounted_total = p_order_total;
    END IF;
END$$

DELIMITER ;

This kind of branching business logic is exactly what I like to centralize in a procedure — every application that needs to calculate a discounted total calls the same procedure and gets the same answer, instead of three separate teams reimplementing the discount rules slightly differently.

Example 4: Loops and Cursors

For row-by-row processing, procedures support LOOP, WHILE, REPEAT, and cursors for iterating over a result set.

DELIMITER $$

CREATE PROCEDURE RecalculateAllOrderTotals()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE cur CURSOR FOR SELECT order_id FROM orders;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_order_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE orders o
        SET o.total = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = v_order_id)
        WHERE o.order_id = v_order_id;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

I’ll be honest — I reach for cursors as a last resort. Set-based SQL (a single UPDATE ... JOIN statement) is almost always faster than row-by-row cursor processing in MySQL, so I only use a cursor when the logic genuinely can’t be expressed as a set-based statement.

Error Handling in Procedures

DELIMITER $$

CREATE PROCEDURE TransferFunds(IN p_from INT, IN p_to INT, IN p_amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;

    IF (SELECT balance FROM accounts WHERE account_id = p_from) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    COMMIT;
END$$

DELIMITER ;

The EXIT HANDLER FOR SQLEXCEPTION catches any error during the procedure body, rolls back the transaction, and re-raises the error with RESIGNAL so the calling application still sees it failed rather than silently swallowing the problem.

Stored Procedures vs. Stored Functions vs. Triggers

I use functions when I need a reusable calculation inside a SELECT (like a custom tax calculation), procedures for multi-step operations invoked explicitly, and triggers for automatic side effects tied to table events.

Execution Flow Diagram

flowchart TD
    A[Application calls CALL procedure_name] --> B[MySQL retrieves compiled procedure]
    B --> C[Execute procedure body statements in order]
    C --> D{Error raised?}
    D -->|Yes, with handler| E[Run exception handler logic]
    D -->|No| F[Return OUT params / result sets to caller]
    E --> F

Managing Procedures

-- List stored procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = 'mydatabase';

-- View a procedure's definition
SHOW CREATE PROCEDURE GetCustomersByCity;

-- Modify a procedure (MySQL requires drop and recreate; there's no ALTER PROCEDURE for the body)
DROP PROCEDURE IF EXISTS GetCustomersByCity;
CREATE PROCEDURE GetCustomersByCity(...) BEGIN ... END;

One thing that caught me off guard early on: ALTER PROCEDURE in MySQL can only change characteristics like comments or SQL security context — it cannot change the procedure’s actual body. Any logic change requires dropping and recreating it.

Security: Granting Execute Privilege

GRANT EXECUTE ON PROCEDURE mydatabase.GetCustomersByCity TO 'app_user'@'10.0.0.%';

This lets app_user call the procedure without needing direct SELECT privilege on the underlying customers table — a pattern I use often for exposing carefully controlled read or write operations to lower-trust accounts, like a support tool that should only be able to run pre-approved queries.

Real-World DBA Scenarios

• Batch reporting jobs: procedures that compute and populate summary tables on a schedule, called from a MySQL event or an external scheduler.
• Centralized business rules: billing calculations, discount logic, and eligibility checks that multiple services need to apply identically.
• Controlled data access: giving a third-party integration EXECUTE rights on a narrowly-scoped procedure instead of direct table access.
• Complex multi-table writes: procedures that wrap several related INSERT/UPDATE statements (like order creation touching orders, order_items, and inventory) inside one transactional unit.

Common Pitfalls

• Overusing procedural (row-by-row) logic where a single set-based SQL statement would be simpler and faster.
• Version control blind spots: procedure definitions living only in the database, disconnected from the application’s source control, leading to drift between environments. I always keep procedure CREATE scripts checked into the same repository as the application code, applied through migrations.
• Debugging difficulty: procedures don’t have a rich debugger the way application code often does — I lean on SELECT statements for intermediate variable inspection during development, and thorough logging for production issues.
• Portability: procedure syntax is highly MySQL-specific, so heavy reliance on procedures can make a future database migration more painful.

Troubleshooting Table

FAQs

Can a stored procedure call another stored procedure? Yes, procedures can call other procedures, which is useful for composing smaller reusable pieces of logic.

Can I use a stored procedure inside a SELECT statement? No — procedures are invoked with CALL and can’t be embedded inside a SELECT expression; use a stored function for that.

Do stored procedures support transactions? Yes, a procedure body can include START TRANSACTION, COMMIT, and ROLLBACK, unlike triggers, which cannot use explicit transaction control.

How do I return multiple result sets from one procedure? Simply include multiple SELECT statements in the procedure body — most client libraries support reading multiple result sets from a single CALL.

Are stored procedures faster than equivalent application-side SQL? They can reduce network round-trips for multi-statement operations and benefit from execution plan caching, but the performance difference is usually modest compared to the maintainability and security benefits.

Interview Questions

1. What’s the difference between a stored procedure and a stored function?
2. How do IN, OUT, and INOUT parameters differ?
3. Why might set-based SQL be preferred over cursor-based row processing inside a procedure?
4. How would you grant a low-privilege account the ability to run a specific procedure without direct table access?
5. How does MySQL handle errors inside a procedure, and what does RESIGNAL do?
6. Why is there no ALTER PROCEDURE ... AS to change a procedure’s body?
7. What’s a practical example of using a procedure to enforce transactional consistency across multiple tables?

Optimization Tips

• Avoid cursors and row-by-row loops when a single set-based UPDATE/INSERT ... SELECT statement can achieve the same result.
• Keep procedures focused on a single responsibility; compose smaller procedures rather than writing one enormous procedure handling many unrelated concerns.
• Index the columns used in a procedure’s internal queries exactly as you would for any other query — procedures don’t get special optimizer treatment.
• Profile procedures with EXPLAIN on their internal queries individually during development, since debugging performance inside a running procedure is harder than debugging a standalone query.

Summary and Key Takeaways

Stored procedures let me centralize business logic directly in the database, guaranteeing every caller — regardless of programming language — gets identical behavior, while also enabling tighter security through EXECUTE-only grants. They support full procedural control flow: variables, conditionals, loops, cursors, and structured error handling with transaction control. The lesson that’s saved me the most trouble is favoring set-based logic over cursors whenever possible, and keeping procedure definitions under version control alongside the rest of the application, so database logic doesn’t silently drift out of sync with the rest of the system.

References

• MySQL 8.0 Reference Manual — Stored Objects
• MySQL 8.0 Reference Manual — CREATE PROCEDURE and CREATE FUNCTION Syntax
• MySQL 8.0 Reference Manual — Cursors
• MySQL 8.0 Reference Manual — Condition Handling