Deconstructing table-valued functions

A User-Defined Function (UDF) is like a stored procedure in that it is a block of T-SQL statements saved as an object, but it differs in that it does not generate a result set; it returns a value of a specified type. A scalar UDF is a function that returns a single value; a Table-Valued Function (TVF) is a function that returns a table.

There are two types of TVFs in the SQL Database Engine:

• Multi-statement TVFs (MSTVFs): MSTVFs declare a return table type, populate the table, then return the table at the end of the function
• Inline TVFs: You can think of an inline TVF as a view that takes a parameter, the body of the function is a single query, and the return value is the result of that query

The following is an example of an MSTVF that we can create in the AdventureWorks sample database:

CREATE OR ALTER FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
    EmployeeID int...