MySQL Stored Procedures Explained — IN, OUT, INOUT, IF, Loops & More

A stored procedure is just a bunch of SQL statements saved on the server under one name. Instead of sending five queries from your app, you send one CALL and MySQL runs everything server-side.

Table of Contents

• Creating a Procedure
• Variables
• Parameters (IN, OUT, INOUT)
• Control Flow (IF, CASE, Loops)
• Calling a Procedure
• Dropping or Updating a Procedure
• Putting It All Together

Creating a Procedure

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- your SQL here
END //

DELIMITER ;

The DELIMITER thing trips everyone up at first. MySQL normally treats ; as the end of a statement. But your procedure has ; inside it — so you switch the delimiter to // temporarily, write the procedure, then switch back.

Here's a simple one:

DELIMITER //

CREATE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //

DELIMITER ;

Run that once, and now GetTotalUsers lives on the server. Call it anytime.

Variables

Variables hold temporary data while your procedure runs.

DECLARE variable_name datatype [DEFAULT value];

DELIMITER //

CREATE PROCEDURE ShowUserCount()
BEGIN
    DECLARE user_count INT DEFAULT 0;

    SELECT COUNT(*) INTO user_count FROM users;

    SELECT user_count AS 'Total Users';
END //

DELIMITER ;

Three keywords to remember:

• DECLARE — creates the variable
• SET — assigns a value
• SELECT ... INTO — stuffs a query result into a variable

One tip: prefix your variable names (like v_count or _count). If your variable has the same name as a column, MySQL gets confused.

Parameters (IN, OUT, INOUT)

Parameters let you pass data in and out of a procedure.

IN — the most common

DELIMITER //

CREATE PROCEDURE GetUserByEmail(IN user_email VARCHAR(100))
BEGIN
    SELECT id, name, email
    FROM users
    WHERE email = user_email;
END //

DELIMITER ;

OUT — read the result later

DELIMITER //

CREATE PROCEDURE CountUsersByStatus(IN status_val VARCHAR(20), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total
    FROM users
    WHERE status = status_val;
END //

DELIMITER ;

When you call it, use a session variable (@whatever) to catch the output:

CALL CountUsersByStatus('active', @result);
SELECT @result;

INOUT — two-way

DELIMITER //

CREATE PROCEDURE IncreaseScore(INOUT score INT, IN amount INT)
BEGIN
    SET score = score + amount;
END //

DELIMITER ;

SET @my_score = 10;
CALL IncreaseScore(@my_score, 5);
SELECT @my_score;  -- shows 15

Control Flow (IF, CASE, Loops)

IF-THEN-ELSE

DELIMITER //

CREATE PROCEDURE CheckStock(IN product_id INT)
BEGIN
    DECLARE stock_count INT;

    SELECT quantity INTO stock_count
    FROM inventory
    WHERE id = product_id;

    IF stock_count = 0 THEN
        SELECT 'Out of stock' AS status;
    ELSEIF stock_count < 10 THEN
        SELECT 'Low stock' AS status;
    ELSE
        SELECT 'In stock' AS status;
    END IF;
END //

DELIMITER ;

CASE

Good when you have more than a couple conditions.

DELIMITER //

CREATE PROCEDURE GetDiscount(IN customer_type VARCHAR(20))
BEGIN
    CASE customer_type
        WHEN 'premium' THEN SELECT 0.20 AS discount_rate;
        WHEN 'regular' THEN SELECT 0.10 AS discount_rate;
        ELSE SELECT 0.00 AS discount_rate;
    END CASE;
END //

DELIMITER ;

Loops

WHILE — runs as long as the condition is true:

DELIMITER //

CREATE PROCEDURE GenerateNumbers(IN limit_val INT)
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= limit_val DO
        SELECT counter AS number;
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

REPEAT — runs until the condition becomes true:

DELIMITER //

CREATE PROCEDURE Countdown(IN start_val INT)
BEGIN
    DECLARE counter INT DEFAULT start_val;

    REPEAT
        SELECT counter AS countdown;
        SET counter = counter - 1;
        UNTIL counter < 0
    END REPEAT;
END //

DELIMITER ;

The difference: WHILE checks the condition before running, REPEAT checks after (so REPEAT always runs at least once).

Calling a Procedure

Simple — use CALL:

CALL GetTotalUsers();
CALL GetUserByEmail('john@example.com');
CALL CountUsersByStatus('active', @count);

Procedures can call other procedures too:

DELIMITER //

CREATE PROCEDURE ProcessNewUser(IN email VARCHAR(100), IN name VARCHAR(100))
BEGIN
    INSERT INTO users (email, name) VALUES (email, name);
    CALL LogActivity(email, 'user_created');
END //

DELIMITER ;

Dropping or Updating a Procedure

DROP PROCEDURE IF EXISTS GetTotalUsers;

IF EXISTS saves you from errors if someone already deleted it.

In MySQL 8.0+ you can also do this instead of drop + recreate:

DELIMITER //

CREATE OR REPLACE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS total FROM users WHERE active = 1;
END //

DELIMITER ;

Putting It All Together

Here's a procedure that places an order — checks stock, inserts the order, updates inventory, and tells you what happened.

DELIMITER //

CREATE PROCEDURE PlaceOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_message VARCHAR(100)
)
BEGIN
    DECLARE v_price DECIMAL(10, 2);
    DECLARE v_stock INT;

    -- check stock and get price
    SELECT price, quantity INTO v_price, v_stock
    FROM products WHERE id = p_product_id;

    IF v_stock >= p_quantity THEN
        INSERT INTO orders (user_id, product_id, quantity, total)
        VALUES (p_user_id, p_product_id, p_quantity, v_price * p_quantity);

        SET p_order_id = LAST_INSERT_ID();

        UPDATE products
        SET quantity = quantity - p_quantity
        WHERE id = p_product_id;

        SET p_message = 'Order placed successfully';
    ELSE
        SET p_order_id = 0;
        SET p_message = 'Insufficient stock';
    END IF;
END //

DELIMITER ;

Call it:

CALL PlaceOrder(1, 5, 2, @order_id, @message);
SELECT @order_id, @message;

That's the gist of stored procedures. Start with something small — wrap a query you write often, add a parameter or two, then build up from there.