SQL · Chapter 38 of 42

Stored Procedures

A STORED PROCEDURE is a named block of SQL (and often procedural code) stored in the database. Call it with `CALL name(args)`.

Useful for encapsulating multi-step logic close to the data — but complicates versioning and testing.

Example 1 (sql)

-- Postgres syntax
CREATE OR REPLACE PROCEDURE give_bonus(pct NUMERIC)
LANGUAGE SQL AS $$
  UPDATE employees SET salary = salary * (1 + pct/100);
$$;

CALL give_bonus(5);

Output

Salaries bumped by 5%

Define once, call with an argument.

Example 2 (sql)

-- MySQL syntax
DELIMITER //
CREATE PROCEDURE get_user(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END//
DELIMITER ;
CALL get_user(1);

Output

One user row returned

MySQL stored procedure with a delimiter change.

Key points

Named block of SQL stored in the DB.
Called with CALL name(args).
Can accept IN/OUT parameters.
Great encapsulation, tricky to version-control.

💡 Note: Modern web apps often keep procedures thin — heavy business logic tends to live in the application code for easier testing.

Stored Procedures

Key points

📝 Quick Quiz