What is the difference between functions and procedures in SQL?

General Questions~4 min read

SQL function returns a value and plugs directly into queries. SQL procedure runs a series of actions and returns nothing (or uses output parameters).

Theory

TL;DR

Function is like a calculator: give it input, get one answer back. Procedure is like a script: run it, things happen.
Functions must return exactly one value. Procedures return nothing by default.
Functions go inside SELECT, WHERE, JOIN. Procedures are called with CALL or EXEC.
Need a value for a query? Function. Need to update rows, log, and commit? Procedure.

Quick Example

sql

-- FUNCTION: returns a single value, usable in SELECT
CREATE FUNCTION CalculateBonus(salary DECIMAL)
RETURNS DECIMAL AS
BEGIN
  RETURN salary * 0.1;  -- 10% bonus
END;

-- Works inside a query
SELECT employee_name, CalculateBonus(salary) AS bonus
FROM employees;
-- Output: Alice | 5000, Bob | 7000

-- PROCEDURE: executes statements, returns nothing
CREATE PROCEDURE UpdateSalary(emp_id INT, new_salary DECIMAL)
AS
BEGIN
  UPDATE employees SET salary = new_salary WHERE id = emp_id;
  INSERT INTO audit_log VALUES (emp_id, new_salary, GETDATE());
END;

-- Called as a standalone statement, not inside a query
EXEC UpdateSalary(1, 50000);

Functions integrate into expressions. Procedures execute as a unit and handle side effects.

A function is a composable building block. It slots into a SELECT or WHERE clause and produces a value the query engine can use inline. A procedure is a standalone action executor: it can update rows, insert audit records, and manage transactions, but it cannot sit inside an expression. The line is simple: "get me a value" vs. "do this thing."

When to Use

Function: calculating tax, discount, or age from a birthdate; transforming data for display; any reusable logic that feeds directly into a query result
Procedure: batch operations like monthly payroll; multi-step workflows that touch multiple tables; data migration; anything that needs explicit COMMIT or ROLLBACK

Comparison Table

Aspect	Function	Procedure
Return value	Must return exactly one value	Returns nothing (or output parameters)
Usage in queries	Works in SELECT, WHERE, JOIN, ORDER BY	Cannot appear inside SQL expressions
Invocation	`SELECT FunctionName()`	`CALL ProcedureName()` or `EXEC ProcedureName`
Side effects	Should avoid	Expected and common
Parameters	Input only (typically)	Input and output parameters
Transaction control	Limited	Full (COMMIT, ROLLBACK)
When to use	Calculations, transformations	Batch jobs, multi-step operations

How the Engine Handles This

When you call a function inside a query, the engine evaluates it inline. It substitutes the function call with its return value before running the main query. If the function appears in a WHERE clause over 100,000 rows, it runs 100,000 times. That is a real cost. Procedures run as a compiled unit: the engine hands control to the procedure, executes all statements, and returns. Functions are stateless. Procedures can manage state across statements and control transactions.

Common Mistakes

Mistake 1: Putting side effects inside a function

sql

-- WRONG: function modifies data
CREATE FUNCTION ProcessRefund(order_id INT) RETURNS DECIMAL AS
BEGIN
  UPDATE orders SET status = 'REFUNDED' WHERE id = order_id;
  RETURN 100.00;
END;
-- The optimizer may call this 0, 1, or N times depending on the query plan.
-- The UPDATE fires unpredictably. Use a procedure instead.

Mistake 2: Calling a procedure inside WHERE

sql

-- WRONG: procedures don't return values
SELECT * FROM orders WHERE ProcessOrder(order_id) = 1;
-- This won't compile. ProcessOrder returns nothing.

-- Fix: call separately
EXEC ProcessOrder(1);
SELECT * FROM orders WHERE status = 'PROCESSED';

Mistake 3: Assuming scalar functions are always fast

sql

-- SLOW: GetCustomerTier() runs once per row
SELECT o.order_id, c.name, GetCustomerTier(c.customer_id) AS tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Better: store tier in the table or compute inline
SELECT o.order_id, c.name,
  CASE WHEN c.total_spend > 10000 THEN 'Gold' ELSE 'Standard' END AS tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

I've seen this pattern cause a 10x slowdown on reports that ran fine in dev and fell apart on production data. Always check the execution plan when you use scalar functions on large tables.

Real-World Usage

PostgreSQL: functions for computed columns in queries; procedures for ETL jobs
SQL Server: scalar functions in SELECT for calculated fields; stored procedures for nightly batch jobs like payroll
MySQL: functions for data transformation in queries; procedures for multi-step operations with transaction control
Oracle: functions in expressions and function-based indexes; procedures in PL/SQL packages for administrative tasks

Follow-Up Questions

Q: Can a function call a procedure?
A: No, not in standard SQL. Functions must be side-effect-free, and calling a procedure violates that contract. A procedure can call a function and use its return value.

Q: What is the performance difference between a scalar function and inline SQL?
A: Functions add per-call overhead and run row-by-row when used in a result set. Inline SQL lets the optimizer treat the full set at once. For large datasets, set-based logic in plain SQL usually wins.

Q: How do you handle errors in functions vs procedures?
A: Procedures support full transaction control: COMMIT, ROLLBACK, and error handling with retries. Functions typically raise an exception or return NULL on failure. If you need rollback on error, use a procedure.

Q: Can you index the result of a function?
A: Yes. SQL Server and Oracle support computed columns backed by a function, and those columns can be indexed. PostgreSQL requires the function to be marked IMMUTABLE. Useful for query performance, but adds storage overhead.

Examples

Basic: Bonus Calculation

sql

CREATE FUNCTION CalculateBonus(salary DECIMAL)
RETURNS DECIMAL AS
BEGIN
  RETURN salary * 0.1;
END;

SELECT name, salary, CalculateBonus(salary) AS bonus
FROM employees;
-- name  | salary | bonus
-- Alice | 50000  | 5000.0
-- Bob   | 70000  | 7000.0

The function runs for each row and plugs its result into the output column. No separate call needed.

Intermediate: Order Total with State Tax

sql

-- Calculates order total based on state tax rate and shipping threshold
CREATE FUNCTION CalculateOrderTotal(
  subtotal DECIMAL,
  state VARCHAR(2)
) RETURNS DECIMAL AS
BEGIN
  DECLARE @tax_rate DECIMAL = CASE
    WHEN state = 'CA' THEN 0.0725
    WHEN state = 'TX' THEN 0.0625
    ELSE 0.05
  END;
  DECLARE @shipping DECIMAL = CASE
    WHEN subtotal > 100 THEN 0
    ELSE 10
  END;
  RETURN (subtotal * (1 + @tax_rate)) + @shipping;
END;

SELECT
  order_id,
  subtotal,
  CalculateOrderTotal(subtotal, customer_state) AS total
FROM orders
WHERE order_date > '2025-01-01';
-- order_id | subtotal | total
-- 1001     | 150.00   | 159.38
-- 1002     |  75.00   |  89.38

This works because the function returns one value per call. A procedure could not sit inside a SELECT like this.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?