SQL

Functions in SQL

🧮 Functions in SQL are reusable routines that accept input values, perform a calculation or operation, and return a single value or a table, depending on the function type. SQL provides many built-in functions, and most database systems also allow you to create your own user-defined functions (UDFs).

📖 What are SQL Functions?

SQL functions simplify data processing by encapsulating logic into reusable components. Instead of writing the same calculation or transformation repeatedly, you can call a function wherever it is needed.

Information

Do not confuse built-in SQL functions (such as COUNT(), SUM(), or UPPER()) with user-defined functions (UDFs), which are created by developers to perform custom operations.

🎯 Why Use Functions?

Functions improve code quality and simplify database development.

📌 Reuse business logic.
📌 Reduce duplicate SQL code.
📌 Simplify complex calculations.
📌 Improve query readability.
📌 Return calculated values for use in SQL statements.

📚 Types of SQL Functions

Function Type	Description
Built-in Functions	Provided by the database system (for example, COUNT(), AVG(), LOWER()).
Scalar Functions	Return a single value.
Aggregate Functions	Operate on multiple rows and return one result.
User-Defined Functions (UDFs)	Custom functions created by developers.
Table-Valued Functions	Return a table instead of a single value (supported by some databases).

📝 Basic Syntax (Scalar Function)

The syntax for creating user-defined functions differs across database systems. The following example uses SQL Server syntax.

Create a Scalar Function (SQL Server)

CREATE FUNCTION function_name
(
    @parameter DataType
)
RETURNS ReturnDataType
AS
BEGIN
    RETURN expression;
END;

💡 Example: Calculate a Discounted Price

Create a function that calculates a price after applying a 10% discount.

Scalar Function Example

CREATE FUNCTION CalculateDiscount
(
    @Price DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * 0.90;
END;

▶️ Call the Function

Execute the Function

SELECT dbo.CalculateDiscount(500.00) AS DiscountedPrice;

📊 Result

Input Price	Returned Value
500.00	450.00

📄 Table-Valued Function Example

Some database systems allow functions to return an entire table.

Table-Valued Function (SQL Server)

CREATE FUNCTION GetStudentsByDepartment
(
    @Department VARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM Students
    WHERE Department = @Department
);

▶️ Query a Table-Valued Function

Call a Table-Valued Function

SELECT *
FROM GetStudentsByDepartment('Computer Science');

📊 Function Workflow

Step	Description
Input	The caller passes parameter values.
Processing	The function executes its logic.
Return	A scalar value or table is returned.
Usage	The returned result can be used in SQL queries.

⚖️ Functions vs Stored Procedures

Feature	Function	Stored Procedure
Returns a Value	✅ Always	Optional
Can Return a Table	✅ In supported databases	Can return result sets, but not as a table-valued function.
Can Be Used Inside a SELECT	✅ Yes	❌ No
Primary Purpose	Compute and return values.	Perform database operations.

💼 Real-World Example

An online shopping application calculates sales tax for every order. Instead of repeating the formula in every query, a function computes the tax whenever it is needed.

Sales Tax Function

CREATE FUNCTION CalculateTax
(
    @Amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Amount * 0.18;
END;

🗄️ Database Compatibility

Database System	Function Support
SQL Server	✅ Scalar and table-valued functions.
PostgreSQL	✅ Rich function support with multiple procedural languages.
Oracle	✅ Supports functions using PL/SQL.
MySQL	✅ Supports stored functions that return scalar values.
SQLite	Limited built-in support; custom functions are typically added through the host application.

⚠️ Advantages

✅ Improve code reuse.
✅ Simplify calculations.
✅ Make queries easier to read.
✅ Centralize reusable business logic.
✅ Can be called from multiple SQL statements.

⚠️ Limitations

❌ Syntax varies across database systems.
❌ Complex functions may impact performance if overused.
❌ Some databases restrict what operations functions can perform.
❌ Not every database supports table-valued functions.

Warning

Keep functions focused on calculations or data retrieval. Avoid placing large, complex business workflows inside functions, especially when simpler database objects are more appropriate.

⚠️ Best Practices

Best Practice

Create functions for reusable calculations, use descriptive names, validate input parameters when appropriate, keep functions small and efficient, avoid unnecessary complexity, and understand the capabilities and restrictions of your specific database system.

🚀 Key Points to Remember

📌 SQL functions return a value or, in some databases, a table.
📌 Built-in functions and user-defined functions serve different purposes.
📌 Functions improve code reuse and readability.
📌 Functions can often be used directly inside SQL expressions.
📌 User-defined function syntax differs among database systems.
📌 Choose functions for reusable calculations and stored procedures for broader database operations.

>>"Functions transform reusable logic into simple building blocks that make SQL cleaner, more consistent, and easier to maintain."

Summary

✅ SQL functions are reusable routines that return calculated values or, in some database systems, entire tables. They simplify database development by encapsulating common logic, reducing code duplication, and improving query readability. Built-in functions handle everyday tasks, while user-defined functions allow developers to implement custom business logic.