We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Pre-compiled SQL code stored in the database that can be called by name. Reduces network round trips and keeps business logic close to the data.
In short
A stored procedure is a named block of SQL (and procedural logic like loops and conditionals) saved inside the database, which an application runs by calling its name instead of sending the raw SQL text every time. The database parses and plans it once, then reuses that plan on later calls, so a stored procedure cuts network round trips and keeps data-heavy logic running right next to the data.
In short
A stored procedure is a named block of SQL (and procedural logic like loops and conditionals) saved inside the database, which an application runs by calling its name instead of sending the raw SQL text every time. The database parses and plans it once, then reuses that plan on later calls, so a stored procedure cuts network round trips and keeps data-heavy logic running right next to the data.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Stored Procedure across a run of lessons, not one page. These 4 alone are about 164 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Three thousand updates took 117 ms sent one at a time and 17 inside one CALL. A function that tries to COMMIT fails outright, and an EXCEPTION block inside a loop took 9 ms to 134 while catching nothing.
When network calls fail, and they will, retries turn transient failures into invisible hiccups.
Block storage explained: what it is, why databases like PostgreSQL run on it, block vs file vs object storage, and examples like AWS EBS, with a hands-on lab.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Stored Procedurespay once,
yours for life
A stored procedure is code that lives in the database, not in your application. You define it once with a CREATE PROCEDURE statement, give it a name and some parameters, and from then on any client can run the whole thing by calling that name. Postgres, SQL Server, MySQL, and Oracle all support this, each with its own procedural language: PL/pgSQL in Postgres, T-SQL in SQL Server, and PL/SQL in Oracle.
Unlike a plain SQL query, a procedure can hold real program logic. It can take inputs, declare local variables, branch with IF, loop, run several statements in sequence, open transactions, and return values or result sets. So a single CALL can do work that would otherwise be five or six separate queries fired from your app.
People often confuse stored procedures with functions and views. A view is a saved SELECT that you query like a table. A function returns a value and is usually meant to be used inside a query. A stored procedure is the one that runs a sequence of statements as a unit and can change data, manage transactions, and return multiple result sets.
When you create a procedure, the database stores its definition in the system catalog. The first time it runs, the engine parses the SQL, checks types, and builds an execution plan. Many engines then cache that plan, so the second and later calls skip the parse and planning step and go straight to execution. SQL Server is well known for this plan caching behavior.
The big win is network traffic. Instead of the app sending a long SQL string, waiting, sending the next one, and so on, the client sends one short CALL with a few parameters. If a piece of business logic touches ten rows across three tables, that can collapse ten round trips into one. On a connection where each round trip costs a few milliseconds, that adds up fast under load.
Parameters are passed as typed values, not glued into a SQL string, which is why parameterized procedure calls are naturally resistant to SQL injection. The database treats the input as data, never as code to execute.
Stored procedures shine when logic is data-intensive and chatty. Batch jobs, nightly aggregations, complex multi-step updates wrapped in a transaction, and operations that would otherwise pull thousands of rows over the wire just to filter them in the app are all good fits. Banks and payment systems lean on them heavily for exactly this reason.
The cost is where your logic lives. Business rules baked into PL/SQL or T-SQL are harder to version-control, test, and review than application code, and they tie you to one database vendor since the procedural languages are not portable. A team that puts too much logic in procedures can end up with a second codebase hidden in the database that nobody fully understands.
The common modern stance is to keep most business logic in the application for testability and portability, and reserve stored procedures for the cases where doing the work in the database is clearly faster, such as bulk data movement or tight transactional sequences that you do not want to chatter across the network.
Say you run an online store and a customer places an order. Doing it from the app means: insert the order, insert each line item, decrement inventory for each product, check that no stock went negative, and write a ledger entry. That is many statements and, done naively, many round trips, all of which must succeed or fail together.
A stored procedure named place_order can take the customer id, a list of items, and a payment id, then run all of those statements inside one transaction. The app makes one call. If the stock check fails partway through, the procedure rolls everything back so you never end up with an order that has no inventory reserved.
This is also where procedures earn their keep for consistency. Because the transaction boundaries and the integrity checks live in one place, every client that places an order, whether the web app, a mobile backend, or an internal admin tool, goes through the same logic and cannot skip a step.
Oracle Database
PL/SQL procedures power core banking and ERP systems where complex transactional logic runs inside the database for speed and consistency.
Microsoft SQL Server
T-SQL stored procedures with cached execution plans are standard in enterprise apps to cut round trips and centralize business rules.
PostgreSQL
PL/pgSQL procedures handle batch updates, data migrations, and multi-statement transactions next to the data.
MySQL
Used in high-traffic web backends for set-based operations and bulk updates that would be slow if looped from the application.
A function returns a single value (or a table in some engines) and is normally called inside a query, like SELECT calc_tax(amount). A stored procedure runs a sequence of statements as a unit, can modify data and manage transactions, can return multiple result sets, and is invoked on its own with CALL or EXEC rather than inside a SELECT.
They help, because parameters are passed as typed values, so the database treats input as data, not code. But the protection comes from parameterization, not from the procedure itself. If a procedure builds a query by concatenating strings with dynamic SQL inside it, it can still be injectable.
Often yes for data-heavy, multi-step work, because the plan is cached and you make far fewer network round trips. For a single simple query the difference is negligible. The speedup comes mainly from collapsing many round trips into one call and running logic next to the data.
Logic in procedures is harder to unit test, version control, and code review than application code, and the procedural languages are vendor-specific, so they lock you into one database. Many teams keep business logic in the app and use procedures only for clearly performance-critical database work.
Yes. Procedures can call other procedures and functions, which lets you build reusable building blocks. Most engines support nesting several levels deep, though very deep or recursive nesting can hit configured limits and make debugging harder.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
Transaction
A sequence of database operations treated as a single atomic unit. Either all operations succeed (commit) or none of them do (rollback).
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
BASE
An alternative to ACID for distributed systems: Basically Available, Soft state, Eventually consistent. Trades strong consistency for availability.
This page explains the idea. The "Stored Procedures" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Stored Procedures lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Stored Procedure in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
