Glossary

Stored Procedure

A stored procedure is a subroutine of SQL logic stored directly in the DBMS and called by name. Unlike a regular query — it is compiled once, executes faster, and can have input/output parameters.

Example (MySQL)

DELIMITER $$
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
  SELECT o.id, o.total, o.status
  FROM orders o
  WHERE o.user_id = userId
  ORDER BY o.created_at DESC;
END$$
DELIMITER ;

-- Call
CALL GetUserOrders(42);

Benefits

Logic at the DB level — independent of the application language
Less traffic between application and database
Permissions at the procedure level, not the table level

Drawbacks

Hard to test and version (not in Git)
Business logic scattered between code and database
Tied to a specific DBMS