CREATE FUNCTION – User-Defined Functions in SQL

Syntax

CREATE FUNCTION function_name (param1 DATATYPE, param2 DATATYPE, ...)
RETURNS return_datatype
BEGIN
  -- SQL logic here
  RETURN value;
END;

What Is CREATE FUNCTION?

In SQL, CREATE FUNCTION is used to define a user-defined function (UDF). These functions accept input parameters, perform some operations, and return a single result.

Unlike stored procedures, functions must return a value and can be used directly inside SQL queries, just like built-in functions such as NOW(), ROUND(), or CONCAT().

Why Use a User-Defined Function?

When you have logic that you repeat in multiple queries — such as tax calculations, date differences, or formatting — it’s better to write a function once and reuse it. This keeps your queries clean and your logic consistent.

Example: Price with Tax

Here’s an example where we create a function to calculate total price with tax:

CREATE FUNCTION calculate_total(price DECIMAL(10,2), tax_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
BEGIN
  RETURN price + (price * tax_rate);
END;

Now you can use this function inside a query like this:

SELECT calculate_total(100, 0.05);

Expected Output:

105.00

Where Can You Use It?

In SELECT queries
In WHERE or ORDER BY clauses
Inside VIEWS or other FUNCTIONS
To simplify business logic or calculations

Benefits

Reusability: Define once, use anywhere
Readability: Queries look cleaner
Maintainability: Easier to update logic centrally
Testability: Easier to validate and debug

Summary

CREATE FUNCTION defines a reusable SQL logic block that returns a single value
User-defined functions work like built-in SQL functions
They make your SQL cleaner, more efficient, and easier to manage