Lesson 27 / 28

Stored Procedures at a Glance

Reusable SQL logic saved on the server.

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 replaces the need for indexes
  • It saves reusable SQL logic on the server under a callable name
  • It automatically backs up the database
Answer

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