MySQL Stored Procedures Tutorial

What are Stored Procedures?

A stored procedure is reusable SQL code saved in the database. You call it by name, optionally passing parameters — great for complex, repeated logic.

Creating One

DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
  SELECT * FROM orders WHERE user_id = userId;
END //
DELIMITER ;

Calling It

CALL GetUserOrders(5);

With Output Parameters

DELIMITER //
CREATE PROCEDURE CountOrders(IN uid INT, OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM orders WHERE user_id = uid;
END //
DELIMITER ;

CALL CountOrders(5, @count);
SELECT @count;

Pros & Cons

• ✅ Reusable, runs on the server, can improve performance.
• ❌ Harder to version-control and debug than app code.

FAQs

Procedure vs function?

Functions return a value and can be used in queries; procedures perform actions via CALL. More in our MySQL guides.

Should I put logic in the DB?

Modern apps often keep logic in code, but procedures suit heavy data operations.