User-defined functions in SQL

Introduction

User-defined functions let you create your own reusable commands in SQL. They help you do the same task many times without rewriting code.

When you want to calculate a value repeatedly, like tax or discount.

When you need to format or clean data in a consistent way.

When you want to simplify complex calculations into a single command.

When you want to reuse logic across different queries or reports.

When you want to keep your SQL code organized and easier to read.

Syntax

SQL

CREATE FUNCTION function_name (parameters) RETURNS return_type
BEGIN
  -- function body
  RETURN value;
END;

Functions must return a single value using RETURN.

Parameters are inputs you give to the function to work with.

Examples

This function adds two numbers and returns the result.

SQL

CREATE FUNCTION add_two_numbers(a INT, b INT) RETURNS INT
BEGIN
  RETURN a + b;
END;

This function joins first and last names with a space.

SQL

CREATE FUNCTION get_full_name(first_name VARCHAR(50), last_name VARCHAR(50)) RETURNS VARCHAR(100)
BEGIN
  RETURN CONCAT(first_name, ' ', last_name);
END;

Sample Program

This function calculates the price after applying a discount rate. The SELECT statement shows how to use it.

SQL

CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount_rate DECIMAL(5,2)) RETURNS DECIMAL(10,2)
BEGIN
  RETURN price - (price * discount_rate / 100);
END;

SELECT calculate_discount(100, 15) AS discounted_price;

Important Notes

Not all SQL databases support user-defined functions the same way; check your database documentation.

Functions should be simple and fast because they run inside queries.

Use meaningful names for functions and parameters to keep code clear.

Summary

User-defined functions let you create reusable SQL commands.

They take inputs, do work, and return a single value.

Functions help keep your SQL code clean and efficient.