Why User-defined functions in SQL? - Purpose & Use Cases

The Big Idea

What if you could fix a mistake once and have it corrected everywhere instantly?

The Scenario

Imagine you need to calculate the total price with tax for hundreds of orders in a big spreadsheet. You copy and paste the same formula everywhere, hoping you don't make a mistake.

The Problem

Manually repeating calculations is slow and easy to mess up. If the tax rate changes, you must update every formula by hand. This wastes time and causes errors.

The Solution

User-defined functions let you write the calculation once and reuse it everywhere. Change it once, and all results update automatically. It saves time and avoids mistakes.

Before vs After

✗ Before

SELECT price, price * 1.1 AS price_with_tax FROM orders;

✓ After

CREATE FUNCTION calc_tax(price DECIMAL) RETURNS DECIMAL DETERMINISTIC BEGIN RETURN price * 1.1; END;
SELECT price, calc_tax(price) AS price_with_tax FROM orders;

What It Enables

You can create your own reusable tools inside the database to simplify complex tasks and keep your work consistent.

Real Life Example

A store calculates discounts differently for each product category. Using user-defined functions, they write one discount rule per category and apply it easily to all sales.

Key Takeaways

User-defined functions save time by reusing code.

They reduce errors by centralizing logic.

They make updating calculations easy and fast.