Functions (Transact-SQL)

This section describes the built-in functions and user-defined functions that can be used in Transact-SQL statements.

Types of Functions

Transact-SQL functions are categorized into several types:

• Scalar Functions: Return a single value.
• Aggregate Functions: Perform a calculation on a set of rows and return a single aggregate value.
• Table-Valued Functions: Return a table result set. These can be inline or multi-statement.

Common Built-in Functions

SQL Server provides a rich set of built-in functions for various purposes:

String Functions

These functions manipulate string data. Examples include:

• LEN(): Returns the length of a string.
• LEFT(): Returns the left part of a string.
• RIGHT(): Returns the right part of a string.
• SUBSTRING(): Extracts a part of a string.
• REPLACE(): Replaces occurrences of a substring.
• UPPER(): Converts a string to uppercase.
• LOWER(): Converts a string to lowercase.

SELECT UPPER('sql server') AS UppercaseString;
SELECT LEN('Microsoft') AS StringLength;

Numeric Functions

These functions perform mathematical operations. Examples include:

• ABS(): Returns the absolute value of a number.
• ROUND(): Rounds a number to a specified length or precision.
• CEILING(): Returns the smallest integer greater than or equal to a number.
• FLOOR(): Returns the largest integer less than or equal to a number.
• RAND(): Returns a random float number.

SELECT ROUND(123.456, 2) AS RoundedValue;
SELECT CEILING(7.3) AS CeilingValue;

Date and Time Functions

These functions work with date and time values. Examples include:

• GETDATE(): Returns the current database system date and time.
• DATEPART(): Returns a specified part of a date.
• DATEDIFF(): Returns the difference between two dates.
• DATEADD(): Adds a specified time interval to a date.
• FORMAT(): Formats a date and time value.

SELECT GETDATE() AS CurrentDateTime;
SELECT DATEADD(day, 5, GETDATE()) AS DateAfterFiveDays;

System Functions

These functions return information about the system or current session. Examples include:

• @@ROWCOUNT: Returns the number of rows affected by the last statement.
• @@VERSION: Returns information about the SQL Server version.
• DB_NAME(): Returns the name of the current database.

SELECT @@VERSION AS SQLServerVersion;
SELECT DB_NAME() AS CurrentDatabase;

User-Defined Functions (UDFs)

You can create your own functions in Transact-SQL to encapsulate logic and improve code reusability.

Creating a Scalar User-Defined Function

A scalar UDF returns a single value. Here's an example:

CREATE FUNCTION dbo.CalculateDiscount
(
    @Price DECIMAL(10, 2),
    @DiscountRate DECIMAL(4, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountRate);
END;
GO

-- Example usage:
SELECT dbo.CalculateDiscount(100.00, 0.10) AS FinalPrice;

Creating an Inline Table-Valued Function

An inline TVF returns a table and is defined by a single `SELECT` statement.

CREATE FUNCTION dbo.GetEmployeesByDepartment
(
    @DepartmentName NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @DepartmentName
);
GO

-- Example usage:
SELECT * FROM dbo.GetEmployeesByDepartment('Sales');

Function Permissions

To execute a function, users need the `EXECUTE` permission on the function. Permissions for creating functions typically require `CREATE FUNCTION` permission in the database.