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

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

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.