Introduction to Stored Procedures

A stored procedure is a prepared SQL code block that you save in the database and can reuse by calling its name. Procedures encapsulate complex business logic, reduce code duplication, and improve performance by reducing network round-trips.

Why Use Stored Procedures?

Reusability: Define logic once and call it from many places
Performance: Execution plans are cached after the first run
Security: Grant EXECUTE permission without exposing table structure
Reduced network traffic: Execute multiple statements server-side with one call

Creating a Procedure

Use CREATE PROCEDURE followed by a name, an optional parameter list, and a BEGIN...END block containing the procedure body. The DELIMITER command changes the statement terminator so MySQL does not interpret the semicolons inside the body as the end of the CREATE statement.

Example

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)
BEGIN
    SELECT o.order_id, o.created_at, o.total
    FROM orders o
    WHERE o.customer_id = cust_id
    ORDER BY o.created_at DESC;
END //

DELIMITER ;

-- Call the procedure
CALL GetCustomerOrders(42);

SQL

Full Editor

Pro Tip

Always change the DELIMITER before creating procedures to avoid syntax errors.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned