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

Canonical: https://devsunite.com/resources/core-subjects/dbms/sql-stored-procedures
Last updated: 2026-11-22

Subject: DBMS · Category: SQL · 3 min read

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:

```sql
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:

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

## Related

- [Previous: SQL Subqueries](https://devsunite.com/resources/core-subjects/dbms/sql-subqueries)
- [Next: What Is Transaction Processing?](https://devsunite.com/resources/core-subjects/dbms/what-is-transaction-processing)
- [More in SQL: Introduction to SQL](https://devsunite.com/resources/core-subjects/dbms/introduction-to-sql)
- [More in SQL: SQL Data Types](https://devsunite.com/resources/core-subjects/dbms/sql-datatypes)
- [More in SQL: Basic SQL Commands](https://devsunite.com/resources/core-subjects/dbms/basic-sql-commands)
- [More in SQL: Other SQL Commands](https://devsunite.com/resources/core-subjects/dbms/other-sql-commands)
- [More in SQL: SQL Aggregate Functions](https://devsunite.com/resources/core-subjects/dbms/aggregate-functions)
- [More in SQL: SQL Joins](https://devsunite.com/resources/core-subjects/dbms/sql-joins)
- [More in SQL: SQL Views](https://devsunite.com/resources/core-subjects/dbms/sql-views)
- [More in SQL: SQL Indexes](https://devsunite.com/resources/core-subjects/dbms/sql-indexes)
- [All 174 DBMS interview questions](https://devsunite.com/resources/core-subjects/dbms)
