Lesson 39 of 61

5 mins read

SQL Stored Procedures, Functions & Query Parameters

In Plain English

A `Stored Procedure` is a precompiled collection of SQL statements and procedural logic (loops, if conditions, exception handling) stored directly inside the database server. They enforce security boundaries, reduce network latency, and encapsulate critical multi-step business transactions.

The Mental Model

"A stored procedure is like a saved macro or microservice running directly inside the database engine: instead of sending 10 individual SQL queries over the internet, you send one command: "Execute ProcessPayroll()"."

Deep Dive: How It Works

Procedure vs Function: Stored Procedures are invoked with `CALL proc_name()` and can execute transactions (COMMIT/ROLLBACK); Functions return a scalar value or table and can be used directly inside `SELECT` queries.

Parameters: `IN` (input value passed by caller), `OUT` (return variable set by procedure), `INOUT` (read and updated by procedure).

Security Privilege: Users can be granted `EXECUTE` permissions on a procedure without granting direct read/write access to underlying sensitive tables.

Syntax Blueprint

-- Standard Stored Procedure Structure (PostgreSQL / MySQL):
CREATE PROCEDURE TransferFunds(IN sender INT, IN receiver INT, IN amount DECIMAL)
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amount WHERE id = sender;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver;
  COMMIT;
END;

CALL TransferFunds(101, 202, 500.00);

Stored procedures encapsulate multi-step transactional logic on the database server.

Core Rules to Remember

Reduced Network Latency: Multi-statement business logic executes locally on the database host with zero network round trips.

Security Encapsulation: Lock down raw table access and force application access through audited stored procedures.

Precompiled Execution Plan: Query execution plans are parsed, compiled, and cached for maximum execution throughput.

Common Beginner Traps & How to Fix Them

Mistake:Putting complex application presentation logic into database stored procedures.

Why it happens: Over-relying on database business logic makes unit testing, version control, and horizontal scaling difficult.

How to fix: Use stored procedures for high-integrity data transactions and keep presentation/business routing in application code.

Pro Tip: In SQLite and modern cloud-native apps, view stored procedures conceptually alongside parameterized queries and database triggers.

Live Interactive Example

Hit Run Code to see it live

Simulating Encapsulated Multi-Statement Transaction Logic

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The atomic transaction transferred $200 from Alice to Bob guaranteeing database consistency.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Execute a Transaction Block

Run a transaction updating `balance = balance + 50` on `id = 1` in `wallet`.
Select all from `wallet`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.