Microsoft® SQL Server® 2012 Bible

458

Part IV: Programming with T-SQL

Inline Table-Valued Functions

The second type of user-defi ned function, the inline table-valued function, is similar to a

view. Both are wrapped for a stored SELECT statement. An inline table-valued user-defi ned

function retains the benefi ts 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-defi ned 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

within the FROM portion of a SELECT statement:

SELECT ProductID, Name, TotalOrders

FROM dbo.ufnGetOrderTotalByProductCategory(1)

ORDER BY TotalOrders DESC;

c18.indd 458c18.indd 458 7/30/2012 5:41:46 PM7/30/2012 5:41:46 PM

http://www.it-ebooks.info