Microsoft SQL Server 2012 Bible

Inline Table-Valued Functions

The second type of user-defined function, the inline table-valued function, is similar to a view. Both are wrapped for a stored SELECT statement. An inline table-valued user-defined function retains the benefits of a view, and adds parameters. As with a view, if the SELECT statement is updatable, then the function is also updatable.

Creating an Inline Table-Valued Function

The inline table-valued user-defined function has no BEGIN/END body. Instead, the SELECT statement is returned as a virtual table:

CREATE FUNCTION FunctionName (InputParameters)
RETURNS Table
AS
RETURN (Select Statement);

The following inline table-valued function is similar to ufnGetOrderTotalByProduct, but instead of returning a single order total for a supplied product, it returns a set that includes product name and order total for a provided product category.

CREATE FUNCTION 
 dbo.ufnGetOrderTotalByProductCategory(@ProductCategoryID int)
RETURNS TABLE
AS 
RETURN
  (
   SELECT p.ProductID, p.Name, sum(sod.OrderQty) as TotalOrders
   FROM Production.Product p
    JOIN Sales.SalesOrderDetail sod
     ON p.ProductID = sod.ProductID
    JOIN Production.ProductSubcategory s
     ON p.ProductSubcategoryID = s.ProductSubcategoryID
    JOIN Production.ProductCategory c
     ON s.ProductCategoryID = c.ProductCategoryID
   WHERE c.ProductCategoryID = @ProductCategoryID
   GROUP BY p.ProductID, p.Name
   ); 
GO

Calling an Inline Table-Valued Function

To retrieve data through dbo.ufnGetOrderTotalByProductCategory, call the function ...