How do you use the CREATE FUNCTION statement to create a table-valued function?
FrankMl
Posted by FrankMl
Last Updated: June 14, 2024
SQL Functions
In SQL, a table-valued function (TVF) is used to return a table data type as its result. The CREATE FUNCTION statement is used to define a new function. A table-valued function can be either a "inline" function, which is defined with a single SELECT statement, or a "multi-statement" function, which can contain multiple statements to build up a result table. Here's how to create both types of table-valued functions:
1. Inline Table-Valued Function
An inline table-valued function is defined with a single SELECT statement, and it must return a result set directly. Syntax: 
CREATE FUNCTION schema.function_name (@parameter1 DataType1, @parameter2 DataType2)
RETURNS TABLE
AS
RETURN 
(
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
);
Example: 
CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN 
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);
2. Multi-Statement Table-Valued Function
A multi-statement table-valued function can have multiple SQL statements and returns a table by declaring a table variable and populating it. Syntax: 
CREATE FUNCTION schema.function_name (@parameter1 DataType1, @parameter2 DataType2)
RETURNS @TableVariable TABLE
(
    column1 DataType1,
    column2 DataType2,
    ...
)
AS
BEGIN
    -- Insert data into @TableVariable
    INSERT INTO @TableVariable (column1, column2, ...)
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

    RETURN;
END;
Example: 
CREATE FUNCTION dbo.GetAllEmployeesByDepartment (@DepartmentID INT)
RETURNS @EmployeeTable TABLE
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO @EmployeeTable (EmployeeID, FirstName, LastName)
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID;

    RETURN;
END;
Usage
Once you have created a table-valued function, you can use it in a SELECT statement like this: 
SELECT * FROM dbo.GetEmployeesByDepartment(1);
Or for a multi-statement function: 
SELECT * FROM dbo.GetAllEmployeesByDepartment(2);
Summary
- Inline table-valued functions are defined using a single SELECT statement. - Multi-statement table-valued functions can include multiple SQL statements and must define a table variable to hold the results. - Both types can be used in a SELECT statement to retrieve data.
Default Image