Microsoft® SQL Server® 2012 Bible

460

Part IV: Programming with T-SQL

SQL Server internally creates a new SQL statement from dbo.vwProductOrderTotals and

the calling SELECT statement’s WHERE clause restriction and then generates a query execu-

tion plan.

In contrast, a function allows the restriction to be passed as a parameter to the SELECT

statement:

CREATE FUNCTION dbo.ufnProductOrderTotals (@ProductID nvarchar(100))

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

WHERE p.ProductID = @ProductID

GROUP BY p.ProductID, p.Name

);

To return the order total for a single product, pass the product ID into the function as a

parameter value:

SELECT ProductID, Name, TotalOrders

FROM dbo.ufnProductOrderTotals(782);

Result:

ProductID Name TotalOrders

----------- ----------------------- -----------

782 Mountain-200 Black, 38 2977

Correlated User-Defi ned Functions

The APPLY command may be used with a table-valued user-defi ned function so that the

UDF accepts a different parameter value for each corresponding row being processed by the

main query.

APPLY was introduced in SQL Server 2005 and greatly increases the fl exibility of T-SQL and

especially the implementation of user-defi ned functions. The APPLY command has two

forms. The most common form, the CROSS APPLY, operates much like an inner join. The

CROSS APPLY command joins data from the main query with any table-valued data sets

from the user-defi ned function. If no data is returned from the UDF, then the row from the

main query is also not returned, as shown in the following example:

SELECT t.Name, t.TotalOrders

FROM Production.Product p

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

http://www.it-ebooks.info