Table-valued functions

As the name implies: The table value function returns the table, while the scalar value function can return the base type.

1. Table value functions

The user-defined Table value function returns the table data type. For an inline table-valued function, there is no function subject; the table is the result set of a single SELECT statement.

The following example creates an inline Table value function. The input parameter of this function is the customer (store) ID, and returnProductID,NameAndYTD Total(Total sales of each product sold to the store as of this year) column.

USE AdventureWorks;GOCREATE FUNCTION Sales.fn_SalesByStore (@storeid int)RETURNS TABLEASRETURN (    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'    FROM Production.Product AS P       JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID    WHERE SH.CustomerID = @storeid    GROUP BY P.ProductID, P.Name);GO

The following example calls this function and specifies the customer ID as 602

SELECT * FROM Sales.fn_SalesByStore (602);
 
For a multi-statement table-valued function, the function body defined in the begin... end statement block contains a series of transact-SQL statements. These statements can be generated in a row and inserted into the returned table.
The following example creates a table value function. This function has an input parameterEmployeeIDReturns the list of all employees reported directly or indirectly to the specified employee.
 
USE AdventureWorks;GOCREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)RETURNS @retFindReports TABLE (    EmployeeID int primary key NOT NULL,    Name nvarchar(255) NOT NULL,    Title nvarchar(50) NOT NULL,    EmployeeLevel int NOT NULL,    Sort nvarchar (255) NOT NULL)--Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ASBEGIN   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),        e.Title,        e.EmployeeID,        1,        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)     FROM HumanResources.Employee AS e          JOIN Person.Contact AS c ON e.ContactID = c.ContactID      WHERE e.EmployeeID = @InEmpID   UNION ALL     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +        c.FirstName + ' ' + c.LastName),        e.Title,        e.EmployeeID,        EmployeeLevel + 1,        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +                  LastName)     FROM HumanResources.Employee as e          JOIN Person.Contact AS c ON e.ContactID = c.ContactID          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID    )-- copy the required columns to the result of the function    INSERT @retFindReports   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort   FROM DirectReports    RETURNEND;GO
In the following example, this function is called.
-- Example invocationSELECT EmployeeID, Name, Title, EmployeeLevelFROM dbo.fn_FindReports(109)ORDER BY Sort;
2. scalar functions
Write a scalar value function alter function [DBO]. [testgetsubnodes _]
(
@ Nodeid int
)
Returns int
Asbegin
Declare @ nodecount int
Select @ nodecount = 5 from menutree
Return @ nodecount
End
This function returns an integer value and can be called in the stored procedure. However, the calling method is different, as the preceding table value function call does not require the owner, you only need to write the function name. For scalar functions, you need to add the owner. For example, the owner is dboselect DBO. testgetsubnodes _, so that 5 is returned. If DBO is not added, the SQL statement does not recognize this function.