Stored Procedure

A stored procedure is a named set of SQL statements saved inside the database that can be run again and again with a single call, optionally with input and output parameters. It works like a function in a programming language, but it runs on the database server.

Stored procedures reduce network traffic because many statements run in one call, keep business rules in one place, and improve security, since users can be allowed to run a procedure without direct access to the tables. Unlike a function, a stored procedure is run with CALL or EXEC and does not have to return a value.

DELIMITER //
CREATE PROCEDURE GetOrdersByCustomer(IN customerId INT)
BEGIN
  SELECT id, order_date, total
  FROM orders
  WHERE customer_id = customerId
  ORDER BY order_date DESC;
END //
DELIMITER ;

CALL GetOrdersByCustomer(42);