SQL Stored Procedures
A SQL stored procedure is a named block of SQL, often with procedural logic such as conditionals and loops, saved inside the database itself. The application executes it by calling its name with parameters instead of sending the full SQL every time, so the logic is defined once and reused.
A stored procedure is a block of SQL (and often some procedural logic, like conditionals and loops, depending on the database) saved inside the database under a name, so it can be executed by calling that name instead of sending the entire logic over the network every time.
Why stored procedures exist
Normally, an application sends a full SQL statement to the database every time it needs something done. A stored procedure flips that: the logic is defined once, inside the database, and the application just calls it by name with some parameters, letting the database do the rest without needing to know the internal SQL at all.
A worked example (PostgreSQL syntax)
A procedure that transfers money between two accounts, wrapping the whole thing in one transaction internally:
CREATE PROCEDURE transfer_funds(sender_id INT, receiver_id INT, amount DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;
END;
$$;
Calling it is a single, simple statement from the application:
CALL transfer_funds(101, 205, 150.00);
The application never has to send the two individual UPDATE statements itself, it just calls transfer_funds with the right parameters, and the procedure (already defined and stored in the database) does the rest.
The real tradeoffs
Stored procedures reduce network round-trips (one call instead of several separate statements) and centralize logic that might otherwise be duplicated across multiple applications hitting the same database. But they also move business logic out of application code and into the database itself, which many teams consider a real downside: it's harder to version-control alongside the rest of the codebase, harder to unit test in isolation, and creates a tighter, more database-specific coupling (stored procedure syntax varies significantly between PostgreSQL, MySQL, and SQL Server, unlike most standard SQL).
Where this matters in real systems
Stored procedures are common in older enterprise systems and in databases shared by multiple applications or teams, where centralizing certain logic in one place (rather than duplicating it across every application that touches the data) is genuinely valuable. Many modern application-centric teams intentionally avoid heavy use of stored procedures, preferring to keep business logic in application code where it's easier to test, review, and deploy alongside everything else, a real, debated architectural choice rather than a settled best practice.
What interviewers actually ask about stored procedures
- "What is a stored procedure?" A named, reusable block of SQL logic saved inside the database, callable by name instead of resending the full logic each time.
- "What's a real benefit of using a stored procedure?" Fewer network round-trips (one call instead of several statements) and centralized logic shared across multiple applications hitting the same database.
- "What's a real downside?" Business logic moves out of application code into the database, making it harder to version-control, test, and it's less portable since stored procedure syntax varies significantly between database systems.
- "When might a team deliberately avoid stored procedures?" When they prefer keeping all business logic in application code for easier testing, code review, and deployment alongside the rest of the codebase.
Key takeaways
- A stored procedure is reusable SQL logic saved inside the database, called by name with parameters.
- It reduces network round-trips and centralizes logic shared across multiple applications.
- The real tradeoff is moving logic out of application code, making it harder to test and version, and less portable across database systems.
- Whether to use stored procedures heavily is a genuine, debated architectural choice, not a universal best practice.
More in SQL
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What is a stored procedure?
A named, reusable block of SQL logic saved inside the database, callable by name instead of resending the full logic each time.
What's a real benefit of using a stored procedure?
Fewer network round-trips (one call instead of several statements) and centralized logic shared across multiple applications hitting the same database.
What's a real downside?
Business logic moves out of application code into the database, making it harder to version-control, test, and it's less portable since stored procedure syntax varies significantly between database systems.
When might a team deliberately avoid stored procedures?
When they prefer keeping all business logic in application code for easier testing, code review, and deployment alongside the rest of the codebase.