SQL Stored Procedures

A stored procedure is a named, precompiled block of SQL (and often procedural logic like variables, loops, and conditionals) that lives inside the database itself rather than in your application code. Instead of sending a raw query string every time you need to run a piece of business logic, you call the procedure by name and let the database execute the logic it already has stored. This matters because it moves repeated logic closer to the data, reduces network round-trips, centralizes business rules, and lets the database reuse a compiled execution plan instead of re-parsing SQL on every call.

Overview: What Are Stored Procedures?

Think of a stored procedure as a function that lives inside the database engine. You define it once with CREATE PROCEDURE, and from then on any application, script, or other procedure can invoke it with CALL (MySQL/PostgreSQL) or EXEC (SQL Server) instead of shipping the full SQL text across the network every time. A procedure can accept input parameters, produce output parameters, run multiple statements in sequence, use conditional logic (IF/CASE), loop over rows, manage its own transactions with COMMIT/ROLLBACK, and even call other procedures.

Stored procedures are different from views and plain queries in an important way: a view is just a saved SELECT that gets expanded inline wherever it’s used, while a stored procedure can contain arbitrary control flow and side effects (inserts, updates, deletes, multiple result sets). They’re also different from user-defined functions, which typically must return a single value or table and can be used inside a SELECT expression, whereas procedures are invoked as standalone statements.

Every major relational database implements stored procedures with its own procedural language: MySQL uses a subset of SQL/PSM with BEGIN...END blocks, PostgreSQL uses PL/pgSQL (or other pluggable languages), and SQL Server uses Transact-SQL (T-SQL). SQLite, the engine used to verify the runnable examples in this lesson, does not support stored procedures at all — it deliberately keeps the engine embeddable and simple, pushing procedural logic into the host application or into triggers/views instead. That’s why every stored-procedure example below is shown as illustrative, non-executable syntax, paired with an equivalent plain SQL script that you can actually run to see the underlying logic in action.

Syntax

The general shape of a stored procedure, using MySQL-style syntax as the reference point:

DELIMITER $$

CREATE PROCEDURE procedure_name (IN param1 TYPE, OUT param2 TYPE)
BEGIN
    -- one or more SQL statements
    SELECT ...;
END$$

DELIMITER ;

CALL procedure_name(value1, @out_var);

DELIMITER — (MySQL only) temporarily changes the statement terminator from ; to something like $$ so the semicolons inside the procedure body don’t end the CREATE PROCEDURE statement early.
CREATE PROCEDURE procedure_name(…) — declares the procedure and its parameter list.
IN / OUT / INOUT — parameter modes. IN passes a value in (default), OUT returns a value to the caller, INOUT does both.
BEGIN … END — the procedure body, one or more statements.
CALL (MySQL, PostgreSQL) or EXEC (SQL Server) — invokes the procedure.

Feature	MySQL	PostgreSQL	SQL Server
Create keyword	`CREATE PROCEDURE`	`CREATE PROCEDURE`	`CREATE PROCEDURE`
Procedural language	SQL/PSM	PL/pgSQL	T-SQL
Invoke	`CALL`	`CALL`	`EXEC` / `EXECUTE`
Needs DELIMITER trick?	Yes	No (uses `$$` dollar-quoting)	No (uses `GO` batch separator)
Output values	`OUT` parameters	`OUT` parameters or return types	`OUTPUT` parameters or `RETURN`

Examples

Example 1: A simple lookup procedure

A common use of a stored procedure is wrapping a parameterized lookup so applications never have to hand-build the query string. In MySQL:

DELIMITER $$

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT id, name, salary
    FROM employees
    WHERE department = dept_name
    ORDER BY salary DESC;
END$$

DELIMITER ;

CALL GetEmployeesByDepartment('Engineering');

That syntax won’t run on every engine (and not at all on SQLite), so here is the underlying logic as a plain, fully runnable script — exactly what the procedure body executes when called with 'Engineering':

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice Chen', 'Engineering', 95000),
  (2, 'Bob Nguyen', 'Engineering', 88000),
  (3, 'Carla Diaz', 'Sales', 72000),
  (4, 'David Kim', 'Marketing', 68000);

SELECT id, name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

Result:

id	name	salary
1	Alice Chen	95000
2	Bob Nguyen	88000

The parameter dept_name plays the same role as the literal 'Engineering' in the plain query — the procedure just lets the caller supply it safely at call time instead of it being hardcoded.

Example 2: A procedure with side effects and a transaction

Procedures often do more than SELECT — they can update data and manage a transaction around it. In PostgreSQL:

CREATE OR REPLACE PROCEDURE give_department_raise(
    dept_name VARCHAR,
    raise_pct NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + raise_pct / 100)
    WHERE department = dept_name;

    COMMIT;
END;
$$;

CALL give_department_raise('Sales', 5);

The equivalent logic, runnable end-to-end:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice Chen', 'Engineering', 95000),
  (2, 'Bob Nguyen', 'Engineering', 88000),
  (3, 'Carla Diaz', 'Sales', 72000),
  (4, 'David Kim', 'Marketing', 68000);

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales';

SELECT id, name, salary FROM employees WHERE department = 'Sales';

Result:

id	name	salary
3	Carla Diaz	75600

Carla’s salary of 72000 becomes 75600 after a 5% raise (72000 × 1.05). Wrapping this in a procedure means every caller applies the raise the same way, and the COMMIT inside the procedure guarantees the update is finalized as a single unit of work.

Example 3: A procedure returning an output value

SQL Server-style procedures commonly use OUTPUT parameters to hand a computed value back to the caller:

CREATE PROCEDURE dbo.GetDepartmentPayroll
    @DeptName VARCHAR(50),
    @TotalPayroll MONEY OUTPUT
AS
BEGIN
    SELECT @TotalPayroll = SUM(salary)
    FROM employees
    WHERE department = @DeptName;
END;
GO

DECLARE @Total MONEY;
EXEC dbo.GetDepartmentPayroll @DeptName = 'Engineering', @TotalPayroll = @Total OUTPUT;
SELECT @Total AS TotalPayroll;

The equivalent aggregate query, runnable directly:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice Chen', 'Engineering', 95000),
  (2, 'Bob Nguyen', 'Engineering', 88000),
  (3, 'Carla Diaz', 'Sales', 72000),
  (4, 'David Kim', 'Marketing', 68000);

SELECT department, SUM(salary) AS total_payroll
FROM employees
WHERE department = 'Engineering'
GROUP BY department;

Result:

department	total_payroll
Engineering	183000

The OUTPUT parameter @TotalPayroll is populated with 183000 (95000 + 88000) and handed back to the caller instead of being returned as a result set row.

How Stored Procedures Work Under the Hood

When you run CREATE PROCEDURE, the database parses the body, checks it for syntax errors, and stores the source (and often a compiled or partially compiled form) in the system catalog. It does not execute anything yet. The real work happens on the first CALL/EXEC:

Parse & bind — the engine resolves table and column references inside the procedure body.
Plan & cache — the query optimizer builds an execution plan for each statement inside the procedure and caches it, keyed by the procedure (and sometimes the parameter values, for parameter-sensitive plans).
Parameter binding — the values passed to IN parameters are substituted safely (never via string concatenation), the same mechanism used by prepared statements, which is why procedures are naturally resistant to SQL injection when used correctly.
Sequential execution — statements inside BEGIN...END run top to bottom, honoring any IF, loops, or early RETURNs in the procedural language.
Transaction control — a procedure can issue its own COMMIT/ROLLBACK, or simply participate in a transaction the caller already started, depending on the engine’s rules.
Result delivery — the procedure can return zero, one, or multiple result sets (from its internal SELECT statements) plus any OUT parameter values, all handed back to the caller when execution finishes.

Because the plan is cached after the first call, subsequent calls skip re-parsing and re-optimizing, which is the main performance argument for stored procedures on very hot code paths — though modern query engines also cache plans for parameterized ad hoc queries and prepared statements, narrowing that gap considerably.

Common Mistakes

Mistake 1: Forgetting to change the delimiter (MySQL)

Without switching the statement delimiter first, MySQL sees the first semicolon inside the procedure body as the end of the CREATE PROCEDURE statement, and the definition fails or is truncated:

-- WRONG: no DELIMITER change, breaks on the first semicolon
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT id, name, salary FROM employees WHERE department = dept_name;
END;

The fix is to temporarily switch the delimiter before defining the procedure, then switch it back:

-- CORRECT
DELIMITER $$

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT id, name, salary FROM employees WHERE department = dept_name;
END$$

DELIMITER ;

Mistake 2: Building SQL with string concatenation instead of parameters

Concatenating untrusted input directly into dynamic SQL inside a procedure reopens the exact SQL-injection risk parameters are meant to close:

-- WRONG: string-concatenated dynamic SQL, vulnerable to injection
SET @sql = CONCAT('SELECT * FROM employees WHERE department = ''', dept_name, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;

Use the parameter directly in a normal statement (or a properly parameterized prepared statement) instead:

-- CORRECT: the parameter is bound, not concatenated
SELECT * FROM employees WHERE department = dept_name;

Best Practices

Always pass values through IN/OUT parameters rather than concatenating them into SQL strings.
Keep each procedure focused on one task — compose small procedures rather than writing one giant procedure that does everything.
Wrap multi-statement writes in an explicit transaction with proper error handling (TRY...CATCH, exception blocks, or engine-specific handlers) so a failure midway doesn’t leave data half-updated.
Document each procedure’s parameters, side effects, and return shape, since its logic is invisible to anyone just reading application code.
Remember procedure syntax is not portable — a MySQL procedure won’t run unmodified on PostgreSQL or SQL Server, and none of it runs on SQLite at all.
Don’t reach for a stored procedure when a view or a single well-written query would do — procedures add operational complexity (deployment, versioning, debugging) that should be justified by a real need such as multi-statement transactions or performance-critical repeated logic.
Grant execute permissions on procedures instead of direct table access when you want to control exactly what operations a given application role can perform.

Practice Exercises

Exercise 1: Using MySQL syntax, write a stored procedure named GetOrdersByCustomer that accepts an IN parameter cust_id and returns all orders for that customer, ordered by order_date. Then write the plain SELECT it would run internally for cust_id = 2.
Exercise 2: In PostgreSQL-style syntax, write a procedure ApplyDiscount that takes a category and a discount_pct, and reduces the price of every product in that category by that percentage. What single UPDATE statement is at the heart of this procedure?
Exercise 3: Spot the bug: a colleague wrote a MySQL procedure with CREATE PROCEDURE ... BEGIN ... END; but never added a DELIMITER change beforehand, and it fails to compile. Explain why it fails and rewrite it correctly.

Summary

A stored procedure is a named, precompiled block of SQL and procedural logic stored inside the database and invoked with CALL or EXEC.
Procedures support input/output parameters, multi-statement bodies, conditional logic, loops, and their own transaction control.
Each database engine has its own procedural language and syntax — MySQL (SQL/PSM), PostgreSQL (PL/pgSQL), SQL Server (T-SQL) — and none of it is portable between engines.
SQLite does not support stored procedures at all; procedural logic there lives in the application layer or in triggers.
The engine parses, binds, and caches an execution plan for a procedure on first call, then reuses that plan on subsequent calls.
Always pass values as bound parameters, never as concatenated strings, to avoid SQL injection.
Reach for a procedure when you need multi-statement transactions or centralized, reusable business logic — not as a default replacement for ordinary queries or views.

SQL Stored Procedures

Overview: What Are Stored Procedures?

Syntax

Examples

Example 1: A simple lookup procedure

Example 2: A procedure with side effects and a transaction

Example 3: A procedure returning an output value

How Stored Procedures Work Under the Hood

Common Mistakes

Mistake 1: Forgetting to change the delimiter (MySQL)

Mistake 2: Building SQL with string concatenation instead of parameters

Best Practices

Practice Exercises

Summary

Table of Contents