Stored Procedures in SQL
Overview
A stored procedure is a precompiled collection of SQL statements and procedural logic stored directly inside the database server. Unlike simple SQL queries or user-defined functions, stored procedures can execute complex procedural control flow (if/else branching, loops, cursor iterations), manage independent transactions (COMMIT and ROLLBACK), accept input and output parameters, and encapsulate complex multi-table operational workflows directly inside the database engine.
What You Will Learn
In this lesson, you will master:
- Procedures vs Functions: The fundamental differences regarding transactions, return values, and execution context.
- Parameter Modes:
IN,OUT, andINOUTparameters across relational dialects. - Transaction Management: Executing atomic
COMMITandROLLBACKoperations within stored procedures. - Procedural Logic: Branching (
IF/ELSE), loops, and error handling (TRY...CATCH/EXCEPTION). - Modern Architectural Trade-offs: When to use stored procedures vs managing logic in application code (Microservices / ORMs).
Stored Procedures vs User-Defined Functions (UDFs)
One of the most frequent points of confusion in SQL architecture is the difference between a Procedure and a Function:
| Feature | Stored Procedure | User-Defined Function (UDF) |
| :--- | :--- | :--- |
| Invocation | Called independently with CALL proc() or EXEC proc | Called inline within SQL statements (SELECT func(col) FROM t) |
| Transaction Control | Can start, commit, and rollback transactions (COMMIT, ROLLBACK) | Cannot control transactions (must execute within calling statement's transaction) |
| Return Value | Can return multiple OUT parameters or zero values | Must return a scalar value or table |
| State Mutation | Designed to execute heavy DML (INSERT, UPDATE, DELETE, DDL) | Typically restricted to pure read calculations (cannot modify database state in standard SQL) |
Practical Example: Multi-Table Account Fund Transfer
A classic use case for a stored procedure is a banking transfer, where funds must be debited from one account and credited to another inside an atomic transaction:
-- PostgreSQL Syntax (PL/pgSQL)
CREATE OR REPLACE PROCEDURE transfer_funds(
p_sender_id INT,
p_receiver_id INT,
p_amount NUMERIC(10, 2)
)
LANGUAGE plpgsql
AS $$
DECLARE
v_sender_balance NUMERIC(10, 2);
BEGIN
-- 1. Check sender balance with row-level lock (FOR UPDATE)
SELECT balance INTO v_sender_balance
FROM accounts
WHERE account_id = p_sender_id
FOR UPDATE;
IF v_sender_balance < p_amount THEN
RAISE EXCEPTION 'Insufficient funds: available %, requested %', v_sender_balance, p_amount;
END IF;
-- 2. Deduct from sender
UPDATE accounts
SET balance = balance - p_amount
WHERE account_id = p_sender_id;
-- 3. Credit receiver
UPDATE accounts
SET balance = balance + p_amount
WHERE account_id = p_receiver_id;
-- 4. Audit ledger entry
INSERT INTO transfer_audit_log (sender_id, receiver_id, amount, transfer_time)
VALUES (p_sender_id, p_receiver_id, p_amount, CURRENT_TIMESTAMP);
-- In procedures, transactions can be explicitly committed!
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'Transfer failed and rolled back: %', SQLERRM;
END;
$$;
-- Executing the procedure:
CALL transfer_funds(101, 202, 500.00);
Output Parameters (OUT & INOUT)
Stored procedures can return output metrics back to the caller without returning full tabular datasets:
-- SQL Server Stored Procedure with OUT parameter
CREATE PROCEDURE GetCustomerOrderStats
@CustomerId INT,
@TotalOrders INT OUTPUT,
@TotalSpend DECIMAL(10, 2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
@TotalOrders = COUNT(*),
@TotalSpend = COALESCE(SUM(order_total), 0.0)
FROM orders
WHERE customer_id = @CustomerId;
END;
-- Calling in T-SQL:
DECLARE @OrdersCount INT, @SpendTotal DECIMAL(10, 2);
EXEC GetCustomerOrderStats
@CustomerId = 42,
@TotalOrders = @OrdersCount OUTPUT,
@TotalSpend = @SpendTotal OUTPUT;
SELECT @OrdersCount AS orders, @SpendTotal AS spend;
Modern Architecture: Stored Procedures vs Application Code
In modern engineering teams, the decision to use stored procedures vs application code involves distinct trade-offs:
Advantages of Stored Procedures:
- Network Efficiency: Complex multi-query operations execute entirely within database memory, eliminating network round-trips between application servers and the database.
- Security & Granular Permissions: You can revoke
SELECT/INSERT/UPDATEpermissions on underlying tables from application roles and grant access only to specific stored procedures, ensuring data can only be modified through audited business logic.
Disadvantages of Stored Procedures:
- Version Control & CI/CD: Managing database code migrations, testing, and rollbacks is significantly more difficult than testing application microservices.
- Scaling Bottlenecks: Application servers (Kubernetes pods) scale horizontally with ease. Relational database servers are expensive to scale vertically; moving heavy compute into stored procedures adds CPU load to the database server.
Common Mistakes & Gotchas
- Procedural Row-by-Row Cursor Loops: Using procedural cursors (
WHILEloops iterating one row at a time) inside a stored procedure defeats the database's vectorized set-based engine. Always write set-basedUPDATE ... FROMorINSERT ... SELECTqueries instead of procedural loops. - Missing Error Handlers: Forgetting to implement explicit transaction rollback logic in error blocks can leave locks active or connections hanging in uncommitted states.
Interview Perspective
SQL Interview Question: When would you use a Stored Procedure versus a User-Defined Function, and why?
Key Talking Points for Candidates:
- Transaction Support: Stored procedures can start, commit, and roll back transactions (
COMMIT / ROLLBACK), making them suitable for complex data modifications. Functions execute inside the caller's transaction and cannot manage transactions independently. - Context of Usage: Functions can be embedded directly inside
SELECTandWHEREclauses (e.g.WHERE total > calculate_tax(amount)), whereas procedures must be invoked with standaloneCALL/EXECcommands. - Data Mutation: Functions are traditionally deterministic and read-only. Procedures are built to modify data (
INSERT,UPDATE,DELETE).
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.