SQL Functions vs Stored Procedures: When to Use Each

1 Creating and Using Functions

You already know stored procedures. A function (also called a user-defined function or UDF) is similar, but with one critical difference: a function always returns a value and can be used inside SQL statements like SELECT, WHERE, and ORDER BY. A procedure cannot.

Creating a Scalar Function (MySQL)

MySQL

DELIMITER //

CREATE FUNCTION calculate_tax(
    amount DECIMAL(10,2),
    tax_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(amount * tax_rate / 100, 2);
END //

DELIMITER ;

Now use it directly in queries — just like a built-in function:

SQL

SELECT
    name,
    salary,
    calculate_tax(salary, 5) AS tax,
    salary - calculate_tax(salary, 5) AS net_salary
FROM employees;

Result

name          | salary  | tax     | net_salary
--------------|---------|---------|----------
Sara Ahmed    | 95000   | 4750.00 | 90250.00
James Wilson  | 88000   | 4400.00 | 83600.00
David Chen    | 105000  | 5250.00 | 99750.00

Function That Returns Text

MySQL

DELIMITER //

CREATE FUNCTION salary_grade(salary DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF salary >= 100000 THEN RETURN 'Executive';
    ELSEIF salary >= 80000 THEN RETURN 'Senior';
    ELSEIF salary >= 60000 THEN RETURN 'Mid';
    ELSE RETURN 'Junior';
    END IF;
END //

DELIMITER ;

-- Use it in WHERE, ORDER BY, GROUP BY — anywhere!
SELECT salary_grade(salary) AS grade, COUNT(*) AS cnt
FROM employees
GROUP BY salary_grade(salary);

Result

grade     | cnt
----------|----
Executive | 1
Senior    | 3
Mid       | 2
Junior    | 1

DETERMINISTIC vs NOT DETERMINISTIC: Mark a function DETERMINISTIC if the same inputs always produce the same output (like a tax calculator). Mark it NOT DETERMINISTIC if it depends on external state (like reading from a table or using NOW()). MySQL requires this declaration.

Dropping a Function

SQLDROP FUNCTION IF EXISTS calculate_tax;

Key Takeaways

Functions always RETURN a value — procedures do not
Functions can be used inside SELECT, WHERE, ORDER BY, GROUP BY
Declare DETERMINISTIC if same inputs always give same outputs
Functions encapsulate reusable calculations (tax, grades, formatting)

2 Functions vs Stored Procedures

Top

Both functions and stored procedures encapsulate reusable SQL logic. But they serve different purposes and have different rules.

Head-to-Head Comparison

Feature	Function	Stored Procedure
Return value	Must return exactly one value	Can return zero, one, or multiple result sets
Use in SELECT	Yes — `SELECT my_func(col)`	No — must use `CALL`
Use in WHERE	Yes — `WHERE my_func(col) > 10`	No
Parameters	Input only (`IN`)	`IN`, `OUT`, `INOUT`
DML allowed	No (MySQL) — can't INSERT/UPDATE/DELETE	Yes — full DML and DDL
Transactions	Cannot manage transactions	Can START TRANSACTION, COMMIT, ROLLBACK
Error handling	Limited	Full TRY-CATCH / HANDLER support
Invocation	`SELECT my_func(args)`	`CALL my_proc(args)`
Best for	Calculations, lookups, formatting	Business logic, data processing, batch operations

When to Use Each

Use a Function When:

You need a reusable calculation that returns one value (tax, discount, grade)
You want to use it inside SELECT, WHERE, or ORDER BY
The logic is read-only — no data modifications
You're building a computed column or custom aggregation

Use a Procedure When:

You need to modify data (INSERT, UPDATE, DELETE)
You need transaction control (COMMIT, ROLLBACK)
You need OUT parameters to return multiple values
You're implementing complex business logic with error handling
You need to return multiple result sets

Side-by-Side Example

As a Function

Function

-- Calculate bonus
CREATE FUNCTION bonus(
    salary DECIMAL(10,2)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN salary * 0.10;

-- Use inline
SELECT name,
  bonus(salary) AS bonus
FROM employees;

As a Procedure

Procedure

-- Apply bonus to DB
CREATE PROCEDURE
  apply_bonuses()
BEGIN
  START TRANSACTION;
  UPDATE employees
  SET salary =
    salary * 1.10;
  COMMIT;
END;

-- Execute
CALL apply_bonuses();

Simple rule: If it calculates and returns a value → function. If it does something (modifies data, sends email, manages transactions) → procedure. When in doubt, use a procedure — it has fewer restrictions.

Performance note: Calling a function for every row in a SELECT (e.g., SELECT my_func(col) FROM million_rows) means the function executes 1 million times. If the function does a subquery inside, that's 1 million subqueries. Keep functions lightweight. For complex logic on large datasets, consider window functions or CTEs instead.

Key Takeaways

Functions return a value and work inside SQL statements (SELECT, WHERE)
Procedures can modify data, manage transactions, and return multiple results
Functions are read-only (in MySQL); procedures have no restrictions
Use functions for calculations; use procedures for actions
Keep functions lightweight — they run once per row

Stored Procedures & Functions

In This Chapter

1 Creating and Using Functions

Creating a Scalar Function (MySQL)

Function That Returns Text

Dropping a Function

Key Takeaways

2 Functions vs Stored Procedures

Head-to-Head Comparison

When to Use Each

Use a Function When:

Use a Procedure When:

Side-by-Side Example

As a Function

As a Procedure

Key Takeaways

All Chapters in SQL Advanced Course

Related Topics

Courses

Services

Support

Connect