What are the advantages and disadvantages of stored procedures?
SHORT ANSWER
Advantages are less network traffic, precompiled plans, central business rules and access control. Disadvantages are harder version control, testing and debugging, database-specific syntax and logic split between app and database.
Explain it simply
A stored procedure is a recipe kept inside the kitchen. Anyone can say "make the monthly report" and the kitchen follows it without carrying ingredients back and forth. But if the recipe is written in the kitchen's own dialect, moving to another kitchen means rewriting it.
Example
sql
-- one call instead of several round trips from the app
CALL place_order(1001, 55, 3);
-- inside: check stock, insert order, reduce stock, write payment entryAdvantages:
- Fewer network round trips for multi-step work.
- Execution plans can be cached.
- Permissions: users get EXECUTE on the procedure without direct table access.
- Rules live in one place and are shared by every application.
- Parameters are typed, which helps against SQL injection (when no dynamic SQL is built inside).
Disadvantages:
- Hard to version, review and test like normal application code.
- Debugging tools are weaker.
- Vendor-specific languages (PL/SQL, T-SQL, PL/pgSQL) cause lock-in.
- Business logic gets split between app and database, and the database becomes a scaling bottleneck.
What to say in the interview
"Procedures reduce round trips and centralise logic and permissions, but they are harder to test, deploy and port. Many modern teams keep logic in the application and use procedures for data-heavy batch work."
Common mistakes
- Saying procedures are always faster. The gain depends on round trips and workload.