Stored Procedure
In one line
A stored procedure is a named block of SQL code stored in the database that can be called with parameters.
In simple words
A stored procedure is a named set of SQL statements saved in the database. You call it by name, optionally passing parameters, and it runs on the database server.
Stored procedures are useful for:
- Encapsulating business logic: keep complex operations in one place on the server.
- Reducing network traffic: instead of sending many queries from the app, call one procedure.
- Security: grant execute permission on a procedure without granting direct table access.
The downside is that logic stored in the database is harder to version control, test, and debug than application code. Many modern applications prefer to keep logic in the app layer and use the database only for storage.
Example
CREATE PROCEDURE transfer_funds(
from_id INTEGER, to_id INTEGER, amount NUMERIC
) AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;
-- Call it
CALL transfer_funds(1, 2, 100);The procedure encapsulates a fund transfer as a single callable operation.
Related terms
Related Resources
Related Lessons
- Stored Procedures
Learn how to write and call stored procedures.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.