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
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.