Transactions & Programmability
Stored Procedures
A stored procedure is a named, precompiled collection of SQL statements and procedural logic stored in the database catalog and invoked by name.
Overview
A stored procedure is a named, precompiled collection of SQL statements and procedural logic stored in the database catalog and invoked by name. It can accept input parameters, return output parameters or result sets, and encapsulate multi-step operations such as validation, data modification, and transaction control. Because the procedure body is parsed and stored once, repeated calls avoid re-parsing the source text, and the logic executes on the server near the data. Stored procedures are part of the SQL-99 standard's persistent stored modules (PSM) facility, though real implementations differ substantially. PostgreSQL offers PL/pgSQL and other languages; Oracle uses PL/SQL; Microsoft SQL Server uses Transact-SQL; MySQL and MariaDB use their own procedural dialects; DB2 supports SQL PL. A procedure differs from a function in that it is invoked with CALL rather than inside an expression, and it may manage transactions. It differs from a view because it contains executable statements, not merely a stored query. It differs from a trigger because it runs on explicit invocation rather than automatically in response to a data event. Procedures are commonly used for batch jobs, complex business rules, and administrative routines.
Syntax and usage
The standard form is CREATE PROCEDURE name (parameter_list) BEGIN ... END, invoked with CALL name(arguments). Parameters are declared with a mode: IN for input, OUT for output, and INOUT for both. The body may contain SQL statements, variable declarations, control flow, exception handlers, and transaction statements. A typical standard-style definition is CREATE PROCEDURE raise_salary(IN emp_id INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = emp_id; END, called as CALL raise_salary(42, 5.0). Vendor dialects diverge. Oracle PL/SQL uses CREATE OR REPLACE PROCEDURE ... IS ... BEGIN ... END; and EXECUTE. SQL Server uses CREATE PROCEDURE ... AS BEGIN ... END and EXEC. PostgreSQL uses CREATE PROCEDURE ... LANGUAGE plpgsql AS $$ ... $$ and CALL. MySQL uses CREATE PROCEDURE ... BEGIN ... END with a DELIMITER change for the client. Output parameters are read differently: SQL Server and PostgreSQL bind them as arguments, while Oracle uses OUT variables in a PL/SQL block. Result sets returned by a procedure are consumed by the calling application, not by a surrounding SQL query.
How it works
When a procedure is created, the database parses it, checks referenced objects, and stores a compiled or normalized representation in the system catalog. At execution, the caller supplies arguments, the engine binds them to parameters, and the procedure body runs as a unit on the server. Variables hold intermediate values, and control-flow constructs such as IF, LOOP, and CASE direct execution. Because the body runs inside the database session, it can read and write tables directly, avoiding network round trips for each statement. Transaction behavior depends on the dialect. In some systems a procedure runs within the caller's transaction and can issue COMMIT or ROLLBACK; in others, transaction control inside a procedure is restricted or forbidden. Procedures may also raise errors, which propagate to the caller and typically abort the current transaction. Security can be managed by granting EXECUTE on the procedure rather than on underlying tables, and some engines support definer's rights versus invoker's rights. Caching of execution plans and dependency tracking also vary, so schema changes may require recompilation.
Examples
A simple procedure that transfers funds between two accounts, using standard-style syntax: CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(12,2)) BEGIN UPDATE accounts SET balance = balance - amount WHERE id = from_id; UPDATE accounts SET balance = balance + amount WHERE id = to_id; END. Invoke it with CALL transfer(1, 2, 100.00). A procedure with an output parameter: CREATE PROCEDURE account_count(OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM accounts; END, called as CALL account_count(?) where the client binds an output variable. A PostgreSQL variant: CREATE PROCEDURE log_access(p_user text) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO access_log(user_name, at) VALUES (p_user, now()); END $$, invoked with CALL log_access('alice'). In SQL Server, the same idea is CREATE PROCEDURE dbo.LogAccess @user nvarchar(50) AS BEGIN INSERT INTO access_log(user_name, at) VALUES (@user, SYSDATETIME()); END, called with EXEC dbo.LogAccess @user = N'alice'. These examples show parameter binding, data modification, and output retrieval across dialects.
Common pitfalls
A frequent mistake is assuming stored procedures are portable. The procedural language, parameter syntax, error handling, and transaction rules differ enough that a procedure written for one engine usually needs rewriting for another. Another pitfall is hiding complex logic in procedures, which makes it harder to test, version, and review than application code. Transaction control inside procedures is risky: issuing COMMIT in the middle of a caller's transaction can break atomicity, and some engines disallow it entirely. Error handling is often incomplete; without explicit handlers, an error aborts the procedure and may leave the caller in an unexpected state. Overusing output parameters instead of result sets complicates client code. Security is another concern: procedures running with definer's rights can bypass row-level permissions, so granting EXECUTE broadly may expose data. Finally, dependency changes can invalidate procedures, and stale cached plans may cause performance surprises after schema or statistics changes. Careful naming, documentation, and testing reduce these risks.
Best practices
Keep procedures focused on a single, well-defined task and give them descriptive names. Prefer parameters over string concatenation to avoid SQL injection and to allow plan reuse. Use explicit transaction boundaries only when the procedure owns the whole unit of work; otherwise let the caller control transactions. Include error handling that logs context and re-raises or returns a clear status. Avoid returning large result sets when a view or query would suffice. Version-control procedure definitions alongside application code and deploy them through migrations. Grant EXECUTE narrowly and prefer invoker's rights when row-level security matters. Document parameters, side effects, and transaction behavior. Test procedures with representative data, including edge cases and concurrent execution. Where logic is simple, consider whether a view, a function, or application code is a better fit. Regularly review dependencies and recompile after schema changes. These practices improve maintainability, security, and predictable performance.
Frequently asked questions
What is the difference between a stored procedure and a function? A procedure is invoked with CALL, may return multiple result sets or output parameters, and can manage transactions in some engines. A function is invoked inside expressions, returns a single value or table, and is typically restricted from modifying data or controlling transactions. Are stored procedures portable across databases? Not generally. SQL-99 defines persistent stored modules, but vendors implement different procedural languages and syntax, so migration usually requires rewriting the body. Do stored procedures improve performance? They can reduce network round trips and reuse parsed plans, but the benefit depends on workload and dialect. They are not automatically faster than equivalent application queries, and poorly written procedures can harm performance. Can a stored procedure call another procedure? Yes, most engines allow nested calls, subject to recursion limits and transaction rules. Nested calls can simplify reuse but also complicate error propagation and debugging.