Stored Procedures

Introduction

A stored procedure is a named block of SQL/PLpgSQL/T-SQL stored in the DB. Used for complex logic close to data, batch operations and admin tasks.

Understanding the topic

Core concepts to understand:

• CREATE PROCEDURE / FUNCTION ....
• Run with CALL proc(args).
• Can have parameters, return values, control flow.
• Procedures vs functions: procedures may not return rows in standard SQL; functions return.
• Live close to data — but harder to version & test than app code.

Syntax reference

Visual workflow / architecture:

CREATE OR REPLACE FUNCTION place_order(uid bigint, amt numeric)
RETURNS bigint AS $
DECLARE oid bigint;
BEGIN
  INSERT INTO orders(user_id, amount) VALUES(uid, amt) RETURNING id INTO oid;
  UPDATE users SET total_spend = total_spend + amt WHERE id = uid;
  RETURN oid;
END;
$ LANGUAGE plpgsql;

SELECT place_order(42, 99.99);

Real-world use

Banks and ERPs rely heavily on stored procedures for complex business logic that must execute close to data.

Best practices

• Use stored procedures for tight, transactional logic.
• Version-control them in your repo.
• Prefer app-side logic when business rules change frequently.

Common mistakes

• Hidden business logic in DB nobody knows about.
• Hard-to-test, untracked changes via DBA console.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Procedure vs function.
• Q2. Pros and cons of stored procedures.
• Q3. PL/pgSQL vs T-SQL.
• Q4. How to test a stored procedure.
• Q5. When NOT to use them.