Stored Procedure

Updated September 30, 20262 min read

In short

A stored procedure is a named set of SQL statements saved inside the database, which applications can run with a single call instead of sending each query.

What is a stored procedure?

A stored procedure is a reusable program that lives inside the database itself. It bundles one or more SQL statements, often with variables, conditions, and loops, under a name, and it can accept input parameters and return results. Applications then run it with a single command such as CALL or EXEC, depending on the database.

When you create a procedure, the database stores its code and, in many systems, can reuse a prepared execution plan for it. Because the logic runs next to the data, a procedure can perform several related steps, such as checking stock, creating an order, and updating inventory, in one network round trip. Each database has its own procedural language for this, such as PL/pgSQL in PostgreSQL or T-SQL in SQL Server.

Think of a stored procedure like a speed-dial button on a phone: instead of dialing every digit each time, you press one button that runs a saved sequence. Stored procedures are common in banking, reporting, and large enterprise systems, where teams want business rules enforced in one place for every application that touches the data. They can also improve security, because users can be allowed to run a procedure without having direct access to the underlying tables.

Stored procedures are often confused with functions and triggers. A database function usually returns a value and can be used inside a query, such as in a SELECT, while a procedure is called on its own and, in many databases, can manage transactions. A trigger runs automatically in response to an event like an INSERT, whereas a procedure runs only when something calls it. The main trade-off is that logic in the database is harder to version, test, and move to another database than logic in application code.

Key takeaways

A stored procedure is named, reusable SQL code saved in the database.
It can take parameters and run many statements in one network round trip.
Each database uses its own procedural language, so procedures are rarely portable.
Procedures can limit access by letting users run them without touching tables directly.
Unlike a trigger, a procedure runs only when it is explicitly called.

Example

Creating and calling a stored procedure in PostgreSQLsql

-- Define a procedure that moves money between two accounts
CREATE PROCEDURE transfer(from_id INT, to_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

-- Run it with one call
CALL transfer(1, 2, 100);

Readers ask

What is the difference between a stored procedure and a function?

A function returns a value and can be used inside a SQL query, while a stored procedure is called on its own with a statement like CALL. In many databases, procedures can also commit or roll back transactions, which functions usually cannot.

Are stored procedures still used?

Yes, especially in data-heavy and enterprise systems where performance and centralized rules matter. Many newer applications keep most business logic in application code instead, because it is easier to test, version, and deploy.

Do stored procedures prevent SQL injection?

They help when user input is passed as parameters, because the input is treated as a value rather than as SQL code. However, a procedure that builds dynamic SQL by concatenating strings can still be vulnerable.