What Is a Stored Procedure?

A stored procedure is a saved set of SQL statements stored in the database itself, which can be called by name and executed as a single unit.

Published November 19, 2026

A stored procedure is a named block of SQL (with variables and logic) stored in the database and executed with a CALL. It reduces round trips and centralises data logic.

The concept

Procedures accept parameters, run multiple statements, use conditionals and loops, and can return results or output parameters.

Pros: performance for chatty workflows, consistent logic and fine-grained permissions. Cons: harder to version, test and port between databases.

One call, many statements

CALL transfer(1, 2, 100);
→ checks balance, debits, credits, logs, commits

Practical examples

MySQL procedure

DELIMITER //
CREATE PROCEDURE transfer(IN src INT, IN dst INT, IN amt DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amt WHERE id = src;
  UPDATE accounts SET balance = balance + amt WHERE id = dst;
  COMMIT;
END //
DELIMITER ;

Call it

CALL transfer(1, 2, 100);

From PHP

$pdo->prepare('CALL transfer(?, ?, ?)')->execute([1, 2, 100]);

Common mistakes

  • Burying business logic in procedures that are not in version control.
  • SQL injection via dynamic SQL inside procedures.
  • Using them where an ORM or service would be simpler.

Key takeaways

  • Stored, reusable SQL with logic.
  • Fewer round trips.
  • Keep them in source control.

FAQ

Procedure vs function?

Functions return a value and can be used in queries; procedures are called with CALL.

Are they secure?

Yes if parameterised; grant EXECUTE only to the right users.

More SQL articles