Stored Procedure

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.