Transact-SQL Functions

Types of Functions

T-SQL supports two primary types of user-defined functions:

• Scalar-Valued Functions: Return a single data value.
• Table-Valued Functions: Return a result set (a table).

Additionally, SQL Server provides a rich set of built-in system functions for various purposes.

Scalar-Valued Functions

Scalar-valued functions return a single value of a defined data type. They can be used in expressions, WHERE clauses, and SELECT lists.

Syntax Example:

CREATE FUNCTION dbo.CalculateDiscount
(
    @Price DECIMAL(10, 2),
    @DiscountRate DECIMAL(4, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @DiscountAmount DECIMAL(10, 2);
    SET @DiscountAmount = @Price * @DiscountRate;
    RETURN @DiscountAmount;
END;
                

Usage Example:

SELECT
    ProductName,
    Price,
    dbo.CalculateDiscount(Price, 0.10) AS DiscountedPrice
FROM
    Products;
                

Table-Valued Functions

Table-valued functions (TVFs) return a table. They are similar to views but offer more flexibility, including accepting parameters.

Types of TVFs:

• Inline Table-Valued Functions (ITVF)
• Multi-Statement Table-Valued Functions (MSTVF)

Inline Table-Valued Functions (ITVF)

ITVFs are more efficient than MSTVFs. They contain a single SELECT statement that returns the table.

Syntax Example:

CREATE FUNCTION dbo.GetEmployeesByDepartment
(
    @DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);
                

Usage Example:

SELECT *
FROM dbo.GetEmployeesByDepartment(101);
                

Multi-Statement Table-Valued Functions (MSTVF)

MSTVFs allow for more complex logic. They define a table variable and then populate it using multiple T-SQL statements before returning it.

Syntax Example:

CREATE FUNCTION dbo.GetHighValueOrders
(
    @MinOrderTotal DECIMAL(10, 2)
)
RETURNS @HighOrders TABLE
(
    OrderID INT,
    OrderDate DATE,
    CustomerName VARCHAR(100),
    TotalAmount DECIMAL(10, 2)
)
AS
BEGIN
    INSERT INTO @HighOrders (OrderID, OrderDate, CustomerName, TotalAmount)
    SELECT
        o.OrderID,
        o.OrderDate,
        c.CustomerName,
        SUM(od.Quantity * od.UnitPrice) AS TotalAmount
    FROM
        Orders o
    JOIN
        Customers c ON o.CustomerID = c.CustomerID
    JOIN
        OrderDetails od ON o.OrderID = od.OrderID
    GROUP BY
        o.OrderID, o.OrderDate, c.CustomerName
    HAVING
        SUM(od.Quantity * od.UnitPrice) >= @MinOrderTotal;

    RETURN;
END;
                

Usage Example:

SELECT *
FROM dbo.GetHighValueOrders(500.00);
                

Creating Functions

Functions are created using the CREATE FUNCTION statement. Key components include:

• CREATE FUNCTION: The command to start function definition.
• function_name: The name of the function, typically schema-qualified (e.g., dbo.MyFunction).
• parameters: Input values the function accepts.
• RETURNS: Specifies the data type for scalar functions or TABLE for table-valued functions.
• AS: Introduces the function body.
• Function Body: Contains the T-SQL logic. For scalar functions, this includes a BEGIN...END block with a RETURN statement. For ITVFs, it's a single SELECT statement. For MSTVFs, it involves declaring a table variable and populating it.

Calling Functions

Functions are called by their name, often schema-qualified. They can be used in various parts of a T-SQL statement:

• SELECT list: To return calculated values.
• WHERE clause: To filter data based on function output.
• JOIN clause: For table-valued functions.
• ORDER BY clause: To sort results.

Unlike stored procedures, functions cannot contain EXECUTE statements or perform DML operations that modify the state of the database (unless they are part of specific trusted function scenarios).

Advantages of Using Functions

• Reusability: Write code once and use it multiple times.
• Modularity: Break down complex logic into smaller, manageable units.
• Readability: Improves the clarity of SQL queries.
• Maintainability: Easier to update and debug logic in one place.
• Parameterization: Allows functions to adapt to different inputs.

Understanding and effectively using T-SQL functions is crucial for efficient database development and administration.