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)
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.

 dbo.ufnGetOrderTotalByProductCategory(@ProductCategoryID int)
   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

Calling an Inline Table-Valued Function

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

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.