Microsoft® SQL Server® 2012 Bible

459

Chapter 18: Building User-Defi ned Functions

18

Result (abridged):

ProductID Name TotalOrders

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

756 Road-450 Red, 44 346

779 Mountain-200 Silver, 38 2394

971 Touring-2000 Blue, 50 322

965 Touring-3000 Yellow, 62 844

762 Road-650 Red, 44 2254

793 Road-250 Black, 44 1642

750 Road-150 Red, 44 437

...

Using Parameters

An advantage of inline table-valued functions over views is the function’s capability to

include parameters within the precompiled SELECT statement. Views, conversely, do not

include parameters, and restricting the result at run time is typically achieved by adding a

WHERE clause to the SELECT statement that calls the view.

The following examples compare adding a restriction to the view to using a function

parameter. The following view returns the total quantity ordered for all products:

USE AdventureWorks2012;

GO

CREATE VIEW dbo.vwProductOrderTotals

AS

SELECT p.ProductID, p.Name, sum(sod.OrderQty) as TotalOrders

FROM Production.Product p

JOIN Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID

GROUP BY p.ProductID, p.Name;

To retrieve the order total for a single product, the calling SELECT statement adds a WHERE

clause restriction when calling the view:

SELECT *

FROM dbo.vwProductOrderTotals

WHERE ProductID = 782;

Result:

ProductID Name TotalOrders

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

782 Mountain-200 Black, 38 2977

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

http://www.it-ebooks.info