Suggest an edit

Improve this article

Refine the answer for “What is the difference between functions and procedures in SQL?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

**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.

### Key Difference

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.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts1138 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.