O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required