SQL Stored Procedures

A stored procedure is a named, precompiled collection of SQL statements (and optional procedural logic) stored in the database that can be executed by name, with optional parameters.

-- PostgreSQL
CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id INT,
    p_new_salary  DECIMAL
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE employees SET salary = p_new_salary WHERE employee_id = p_employee_id;
    COMMIT;
END;
$$;

-- Call it:
CALL update_salary(42, 95000);

Why stored procedures are used:

1. Performance: The query is parsed and its execution plan cached on first call. Subsequent executions reuse the plan (plan caching), avoiding repeated parse/compile overhead.

2. Reduced network traffic: A single CALL replaces sending multiple SQL statements over the network.

3. Security: Applications can be granted EXECUTE permission on a procedure without direct table access — the procedure acts as a controlled API layer.

4. Code reuse and maintainability: Business logic is centralized in the database, callable from multiple applications or services.

5. Encapsulation: Multi-step operations (begin transaction, validate, update, commit) are wrapped in one callable unit.

Stored procedure vs function:

• A procedure performs actions; in SQL standard it cannot return a value (but can use OUT parameters)
• A function returns a value and can be used in SQL expressions (SELECT my_func(x))
• PostgreSQL uses CREATE PROCEDURE (added in v11) for procedures and CREATE FUNCTION for functions; MySQL and SQL Server use CREATE PROCEDURE for both concepts with slight differences

2026 note: Many modern applications rely on ORMs and microservices, reducing stored procedure usage. They remain common in enterprise environments with SQL Server and Oracle.

Syntax varies by database, but the core structure is consistent: define a name, optional parameters, and a body of SQL statements.

SQL Server (T-SQL):

CREATE PROCEDURE GetEmployeesByDept
    @dept_id    INT,
    @min_salary DECIMAL(10,2) = 0  -- default value
AS
BEGIN
    SET NOCOUNT ON;
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = @dept_id
      AND salary >= @min_salary
    ORDER BY salary DESC;
END;
GO

MySQL:

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(
    IN  p_dept_id    INT,
    IN  p_min_salary DECIMAL(10,2),
    OUT p_count      INT
)
BEGIN
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = p_dept_id
      AND salary >= p_min_salary;

    SELECT COUNT(*) INTO p_count
    FROM employees
    WHERE department_id = p_dept_id;
END //
DELIMITER ;

PostgreSQL:

CREATE OR REPLACE PROCEDURE GetEmployeesByDept(
    p_dept_id    INT,
    p_min_salary DECIMAL DEFAULT 0
)
LANGUAGE plpgsql AS $$
BEGIN
    -- PostgreSQL procedures cannot return result sets directly;
    -- use functions with RETURNS TABLE or RETURNS SETOF for that
    UPDATE employees SET last_accessed = NOW()
    WHERE department_id = p_dept_id;
    COMMIT;
END;
$$;

Key elements:

• IN parameters: values passed into the procedure
• OUT parameters: values returned by the procedure (SQL Server, MySQL, Oracle)
• INOUT parameters: passed in and modified (MySQL, Oracle)
• OR REPLACE: overwrites existing definition without dropping first (PostgreSQL, MySQL, Oracle)
• GO (SQL Server): batch separator, not part of the procedure definition

SQL Server / T-SQL:

-- Using EXECUTE (or EXEC shorthand)
EXEC GetEmployeesByDept @dept_id = 10, @min_salary = 70000;

-- Positional parameters
EXEC GetEmployeesByDept 10, 70000;

-- Capturing an OUTPUT parameter
DECLARE @emp_count INT;
EXEC GetEmployeesByDept @dept_id = 10, @min_salary = 0, @count = @emp_count OUTPUT;
PRINT @emp_count;

MySQL:

CALL GetEmployeesByDept(10, 70000, @emp_count);
SELECT @emp_count;  -- retrieve OUT parameter value

PostgreSQL:

-- For stored procedures (CALL, PostgreSQL 11+)
CALL GetEmployeesByDept(10, 70000);

-- For functions (used in SELECT or via PERFORM inside PL/pgSQL)
SELECT * FROM GetEmployeesByDeptFunc(10, 70000);

Oracle:

EXEC GetEmployeesByDept(10, 70000);
-- Or inside PL/SQL:
BEGIN
    GetEmployeesByDept(10, 70000);
END;

Key distinctions:

• SQL Server uses EXEC / EXECUTE
• MySQL and PostgreSQL use CALL
• Oracle uses EXEC in SQL*Plus or BEGIN...END blocks
• In PostgreSQL, CALL is for procedures; functions are called with SELECT or within SQL expressions

Interview note: In PostgreSQL, procedures (using CALL) and functions (using SELECT) are distinct. A procedure can contain COMMIT/ROLLBACK; a function cannot (it runs within the caller's transaction). This distinction was introduced in PostgreSQL 11.

Parameters in a stored procedure serve as the interface between the calling code and the procedure's internal logic. They make procedures flexible and reusable across different inputs.

Three parameter modes:

IN parameters (input only — the default in most databases) Pass a value into the procedure. The procedure can read it but modifications to the parameter do not affect the caller.

CREATE PROCEDURE raise_salary(IN p_emp_id INT, IN p_pct DECIMAL)

OUT parameters (output only) Return a value from the procedure to the caller. The procedure sets the value; the initial value passed in is ignored.

CREATE PROCEDURE get_headcount(IN p_dept INT, OUT p_count INT)
-- Caller: CALL get_headcount(5, @count); SELECT @count;

INOUT parameters (bidirectional) The caller provides an initial value that the procedure can read and modify. The modified value is returned.

CREATE PROCEDURE apply_discount(INOUT p_price DECIMAL, IN p_pct DECIMAL)

Default values (SQL Server):

CREATE PROCEDURE search_employees
    @dept_id    INT = NULL,
    @min_salary DECIMAL = 0
AS ...
-- EXEC search_employees  -- uses defaults
-- EXEC search_employees @dept_id = 5  -- overrides one default

Best practices:

• Use IN parameters for filters and configuration
• Use OUT parameters to return counts, generated IDs, or status codes
• Avoid INOUT when IN + OUT is clearer
• Validate input parameters at the start of the procedure and raise an error for invalid inputs

Stored procedures can improve query performance through several mechanisms, but it is important to understand when and why:

1. Execution plan caching (the primary benefit) The first time a stored procedure is called, the database parses the SQL, generates an execution plan, and caches it. Subsequent calls reuse the cached plan — eliminating the parse/compile overhead.

Ad-hoc queries may be cached too (in SQL Server with "optimize for ad-hoc workloads"), but procedures provide more predictable plan reuse.

2. Reduced network round-trips A single CALL procedure_name(params) replaces sending multiple SQL statements across the network. This matters for high-latency connections or when many small statements would otherwise be batched.

3. Parameter sniffing (a nuance — not always a benefit) SQL Server generates a plan based on the first set of parameter values used to call the procedure. This plan may be optimal for those values but suboptimal for other values (parameter sniffing problem). Mitigations include OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN.

4. Reduced permission overhead Applications can be granted EXECUTE on a procedure rather than SELECT/UPDATE on tables directly, simplifying permission checks at runtime.

5. Server-side execution Logic runs on the database server — reducing data transfer when only summary results are needed by the client.

What stored procedures do NOT guarantee:

• They don't automatically improve a poorly written query inside them
• A bad execution plan cached for a procedure can hurt performance for some parameter combinations
• In PostgreSQL, plan caching for PREPARE/stored functions uses generic plans after 5 executions, which may be less optimal than custom plans

Modern context (2026): Many ORMs and query builders use parameterized queries with prepared statements, which provide similar plan caching benefits without stored procedures.