MySQL Stored Procedures

A stored procedure is a named block of SQL statements stored in the database. Instead of sending the same complex query repeatedly from your application, you define it once as a procedure and call it by name whenever needed. Procedures can accept input values, perform multiple operations, and optionally return output values.

DELIMITER

By default, MySQL uses ; to mark the end of a statement. Inside a procedure, the body also contains semicolons — so MySQL would interpret them as end-of-statement prematurely. To avoid this, temporarily change the delimiter before creating a procedure and restore it after:

DELIMITER $$

CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements here
END$$

DELIMITER ;

Basic Stored Procedure

A procedure that returns all orders above a certain amount:

DELIMITER $$

CREATE PROCEDURE GetLargeOrders()
BEGIN
    SELECT id, customer_id, amount
    FROM orders
    WHERE amount >= 3000
    ORDER BY amount DESC;
END$$

DELIMITER ;

Call it with CALL:

CALL GetLargeOrders();

IN Parameter — Input Only

An IN parameter passes a value into the procedure. It is read-only inside the procedure body:

DELIMITER $$

CREATE PROCEDURE GetOrdersByCity(IN p_city VARCHAR(100))
BEGIN
    SELECT o.id, c.name, o.amount
    FROM orders AS o
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE c.city = p_city;
END$$

DELIMITER ;

-- Call with an argument
CALL GetOrdersByCity('Dhaka');

OUT Parameter — Output Only

An OUT parameter returns a value back to the caller. The procedure writes to it; the caller reads from it via a session variable:

DELIMITER $$

CREATE PROCEDURE GetOrderCount(IN p_city VARCHAR(100), OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM orders AS o
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE c.city = p_city;
END$$

DELIMITER ;

-- Call the procedure and read the output variable
CALL GetOrderCount('Dhaka', @total);
SELECT @total;

INOUT Parameter

An INOUT parameter is both input and output — the procedure reads its initial value and can overwrite it:

DELIMITER $$

CREATE PROCEDURE ApplyDiscount(INOUT p_price DECIMAL(10,2), IN p_pct INT)
BEGIN
    SET p_price = p_price - (p_price * p_pct / 100);
END$$

DELIMITER ;

SET @price = 5000.00;
CALL ApplyDiscount(@price, 10);
SELECT @price;   -- 4500.00

DROP PROCEDURE

DROP PROCEDURE IF EXISTS GetLargeOrders;

Key Points to Remember

• Use DELIMITER to change the statement terminator before defining a procedure, then restore it after.
• IN parameters pass values in (read-only inside). OUT parameters return values out. INOUT do both.
• Call a procedure with CALL procedure_name(args).
• Output values are read via session variables prefixed with @.
• Procedures perform actions (queries, inserts, updates) but do not return a value directly — use functions for that.