Functions (User Defined) - Create Your Own Reusable SQL Logic

Introduction

Built-in SQL functions are powerful, but sometimes they’re not enough. What if you want to calculate grades based on marks or return a student’s age from their date of birth? That’s where User Defined Functions (UDFs) come in. They let you define your own custom logic and reuse it in queries — just like built-in functions.

What is a User Defined Function (UDF)?

A User Defined Function is a named SQL routine that accepts parameters, performs some calculation, and returns a value. It’s like a mini-program you can plug into any SQL query.

Syntax (MySQL Style)

DELIMITER $$

CREATE FUNCTION function_name(parameters)
RETURNS datatype
DETERMINISTIC
BEGIN
  -- function logic
  RETURN value;
END$$

DELIMITER ;

Sample Table – students

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  dob DATE,
  marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '2008-06-12', 85),
(2, 'Diya Iyer', '2009-11-23', 92),
(3, 'Sneha Patil', '2007-05-17', 78),
(4, 'Mehul Agarwal', '2008-01-03', 64);

1. UDF to Calculate Age from DOB

DELIMITER $$

CREATE FUNCTION calculate_age(dob DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, dob, CURDATE());
END$$

DELIMITER ;

Usage:

SELECT name, calculate_age(dob) AS age
FROM students;

name           | age
----------------+-----
Aarav Sharma   | 16
Diya Iyer      | 14
Sneha Patil    | 17
...

2. UDF to Assign Grade Based on Marks

DELIMITER $$

CREATE FUNCTION get_grade(score INT)
RETURNS CHAR(2)
DETERMINISTIC
BEGIN
  DECLARE grade CHAR(2);
  
  IF score >= 90 THEN
    SET grade = 'A+';
  ELSEIF score >= 75 THEN
    SET grade = 'A';
  ELSEIF score >= 60 THEN
    SET grade = 'B';
  ELSE
    SET grade = 'C';
  END IF;
  
  RETURN grade;
END$$

DELIMITER ;

Usage:

SELECT name, marks, get_grade(marks) AS grade
FROM students;

name           | marks | grade
----------------+--------+-------
Aarav Sharma   | 85     | A
Diya Iyer      | 92     | A+
Sneha Patil    | 78     | A
Mehul Agarwal  | 64     | B

3. Drop a Function

DROP FUNCTION IF EXISTS get_grade;

Real-World School Use Cases

• calculate_age – for attendance sheets, eligibility checks
• get_grade – for generating report cards
• Custom get_percentage() or get_status() functions for result dashboards

Best Practices

• Mark functions as DETERMINISTIC if the same input always gives the same result (improves performance).
• Keep functions focused — one task per function.
• Test thoroughly — especially when using inside SELECTs or reports.

Summary

User Defined Functions empower you to extend SQL’s built-in capabilities. Whether you're calculating student ages, assigning grades, or creating reusable logic, UDFs help you write cleaner, smarter queries with your own custom touch.

What’s Next?

Next, we’ll explore SQL Triggers — a way to automate actions in response to data changes like insertions or deletions.