SQL

SQL Functions

Creating user-defined functions (UDFs)

By TechCoder TeamLast updated: 2026-06-02
In a Nutshell

Creating user-defined functions (UDFs) This hands-on tutorial focuses on practical implementation of sql functions concepts.

What are User-Defined Functions (UDFs)?

Unlike Stored Procedures, Functions are designed to return a value. They can be used in SQL statements (like SELECT, WHERE).

Function vs Procedure

| Feature | Function | Stored Procedure | | :--- | :--- | :--- | | Return Value | Must return a value | Optional | | Usage | Can be used in SELECT/WHERE | Cannot be used in SELECT | | Transactions | Cannot use transactions | Can use transactions |

Creating a Scalar Function

A scalar function returns a single value.

Example (MySQL)

Create a function to calculate the yearly salary from a monthly salary:

DELIMITER //
CREATE FUNCTION YearlySalary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE year_sal DECIMAL(10,2);
    SET year_sal = monthly_salary * 12;
    RETURN year_sal;
END //
DELIMITER ;

Using the Function

SELECT EmployeeName, YearlySalary(MonthlySalary) as AnnualPay
FROM Employees;
sql-create-function

Create Tax Function

Problem Statement

Create a function named CalculateTax that takes a salary (INT) and returns 10% of it as tax.

Dropping a Function

DROP FUNCTION IF EXISTS CalculateTax;