Home/Learn/SQL/Stored Procedures & Functions in SQL

Stored Procedures & Functions in SQL

Intermediate

Schema & DDL

Stored procedures and user-defined functions encapsulate reusable SQL logic in the database, reducing round-trips and enforcing consistent business rules at the data layer.

Overview

A stored procedure is a named, compiled block of SQL (and procedural logic) stored in the database catalog. Unlike ad-hoc SQL, it is parsed and planned once then cached, reducing per-execution overhead. Functions return a value and can be used inside SELECT; procedures can have OUT parameters and cannot be used in expressions. In PostgreSQL, CREATE FUNCTION also serves as the procedure mechanism (CREATE PROCEDURE was added in PG11). The debate between application logic vs stored procedures centres on: testability, version control, and language flexibility (app side) vs network round-trips, atomic operations, and DB-side enforcement (procedure side).

Creating Functions and Procedures

PostgreSQL uses PL/pgSQL as its default procedural language. Functions must declare a return type; procedures use CALL syntax and can manage their own transactions with COMMIT/ROLLBACK.

SQL — function and procedure in PostgreSQL

-- PostgreSQL: simple function returning a scalar
CREATE OR REPLACE FUNCTION get_user_order_count(p_user_id BIGINT)
RETURNS INTEGER
LANGUAGE plpgsql
STABLE  -- hint: result is same within a single transaction for same args
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM orders
    WHERE user_id = p_user_id AND status != 'cancelled';
    RETURN v_count;
END;
$$;

-- Usage in a SELECT
SELECT email, get_user_order_count(id) AS order_count
FROM users WHERE status = 'active';

-- PostgreSQL: procedure with transaction control (PG11+)
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_user BIGINT,
    p_to_user   BIGINT,
    p_amount    NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_user;
    UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_user;
    -- COMMIT is implicit at procedure end unless ROLLBACK is called
END;
$$;

CALL transfer_funds(101, 202, 500.00);

When to Use Stored Procedures vs Application Logic

Stored procedures are best for bulk data operations (ETL), atomic multi-step transactions, and generated computed columns. Avoid them for complex business logic that needs unit testing, or logic that varies by consumer.

SQL — bulk operation procedure (PostgreSQL + MySQL)

-- Good use case: bulk soft-delete with audit log in one DB round-trip
CREATE OR REPLACE PROCEDURE archive_old_orders(p_cutoff_date DATE)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders_archive
        SELECT * FROM orders WHERE created_at < p_cutoff_date;

    DELETE FROM orders WHERE created_at < p_cutoff_date;

    INSERT INTO audit_log(action, detail, performed_at)
    VALUES ('ARCHIVE_ORDERS', 'cutoff=' || p_cutoff_date, NOW());
END;
$$;

-- MySQL stored procedure syntax
DELIMITER //
CREATE PROCEDURE get_top_customers(IN p_limit INT)
BEGIN
    SELECT user_id, SUM(total_amount) AS lifetime_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    ORDER BY lifetime_value DESC
    LIMIT p_limit;
END //
DELIMITER ;

CALL get_top_customers(10);

Key Points to Remember

1Functions return a value and can be used in SELECT; procedures cannot be used in expressions.
2Stored procedures reduce network round-trips by executing multi-step logic server-side.
3PL/pgSQL STABLE/IMMUTABLE hints allow the query planner to cache or inline function results.
4PostgreSQL 11+ procedures support COMMIT/ROLLBACK inside the procedure body.
5Avoid stored procedures for business logic requiring unit tests or multi-language consumers.
6Always version-control stored procedures alongside application code in migration scripts.

Interview Questions

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

EasyAmazon

What are the pros and cons of putting business logic in stored procedures?

MediumMicrosoft

How do you call a stored procedure from Spring's JdbcTemplate?

MediumInfosys

Explain the STABLE and IMMUTABLE function volatility categories in PostgreSQL.

HardGoogle

Ask Aria about Stored Procedures & Functions in SQL

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Views & Materialized Views

SELECT & WHERE Clause