Lesson 28 / 30

Stored Procedures at a Glance

Bundle reusable SQL logic into stored procedures with parameters and call them by name.

Defining a procedure

A stored procedure bundles SQL statements under a callable name, optionally taking parameters.

DELIMITER //
CREATE PROCEDURE GetOrdersByCustomer(IN cust_id INT)
BEGIN
  SELECT * FROM orders WHERE customer_id = cust_id;
END //
DELIMITER ;

CALL GetOrdersByCustomer(1);

Quick check: What's the main benefit of a stored procedure?

It saves reusable SQL logic on the server under a callable name
It replaces the need for indexes
It automatically backs up the database
It makes every query use an index

Answer

It saves reusable SQL logic on the server under a callable name — Procedures package logic server-side so apps can just CALL it.