advanced

Stored Procedures in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-209 min readRuntime Verified

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, and INOUT parameters across relational dialects.
  • Transaction Management: Executing atomic COMMIT and ROLLBACK operations 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:

sql
-- 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
-- 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:

  1. Network Efficiency: Complex multi-query operations execute entirely within database memory, eliminating network round-trips between application servers and the database.
  2. Security & Granular Permissions: You can revoke SELECT/INSERT/UPDATE permissions 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:

  1. Version Control & CI/CD: Managing database code migrations, testing, and rollbacks is significantly more difficult than testing application microservices.
  2. 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 (WHILE loops iterating one row at a time) inside a stored procedure defeats the database's vectorized set-based engine. Always write set-based UPDATE ... FROM or INSERT ... SELECT queries 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:

  1. 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.
  2. Context of Usage: Functions can be embedded directly inside SELECT and WHERE clauses (e.g. WHERE total > calculate_tax(amount)), whereas procedures must be invoked with standalone CALL / EXEC commands.
  3. Data Mutation: Functions are traditionally deterministic and read-only. Procedures are built to modify data (INSERT, UPDATE, DELETE).

Interactive Practice Challenge

Challenge: Order Status Cancellation Procedure

Write a stored procedure in PostgreSQL named cancel_order(p_order_id INT) that checks if the order status is 'PENDING'. If so, update the status to 'CANCELLED' and record a log entry in order_audit_log (order_id, action). If the order is already 'SHIPPED' or 'COMPLETED', raise an exception and do not modify the order.

DPA

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.