SQL: User Defined Functions - Inline Table Valued

The next step on our User Defined Functions journey (this didn't start as a series of articles, but a quick demo of the CROSS APPLY statement!) is inline table valued functions.

They are very like a view. So let's start with an example of a view. Again from Northwind, we will work with a view that returns for each order

• Order ID
• Customer ID
• Customer Name
• Order Total (which is calculated from the Order Details)

To package as a view we will have this SQL

CREATE VIEW OrderSummary
AS 
SELECT 
   O.OrderID, C.CustomerID, C.CompanyName,
   CONVERT(money,SUM((OD.UnitPrice * OD.Quantity) * (1-OD.Discount))) AS OrderTotal
FROM Orders O
JOIN [Order Details] OD ON (O.OrderID = OD.OrderID)
JOIN Customers C ON (C.CustomerID = O.CustomerID)
GROUP BY 
 O.OrderID, C.CustomerID, C.CompanyName

Giving us the following output

OrderID     CustomerID CompanyName                              OrderTotal
----------- ---------- ---------------------------------------- ---------------------
10643       ALFKI      Alfreds Futterkiste                      814.50
10692       ALFKI      Alfreds Futterkiste                      878.00
10702       ALFKI      Alfreds Futterkiste                      330.00
...
10998       WOLZA      Wolski  Zajazd                           686.00
11044       WOLZA      Wolski  Zajazd                           591.60

(830 row(s) affected)

Thus we can use this within our SQL statements almost as if it is another table (and thus JOIN to it). If we want to restrict the output we use a WHERE clause, such as

SELECT * FROM OrderSummary
WHERE OrderTotal BETWEEN 140 and 145
ORDER BY OrderTotal

Which corresponds to these five rows

OrderID     CustomerID CompanyName                              OrderTotal
----------- ---------- ---------------------------------------- ---------------------
10809       WELLI      Wellington Importadora                   140.00
10349       SPLIR      Split Rail Beer & Ale                    141.60
10585       WELLI      Wellington Importadora                   142.50
10321       ISLAT      Island Trading                           144.00
10334       VICTE      Victuailles en stock                     144.80

(5 row(s) affected)

Now to do the exact same with a function we would create one, but return a TABLE

CREATE FUNCTION fOrderSummary1()
RETURNS TABLE
AS
RETURN(
   SELECT 
      O.OrderID, C.CustomerID, C.CompanyName,
      CONVERT(money,SUM((OD.UnitPrice * OD.Quantity) * (1-OD.Discount))) AS OrderTotal
   FROM Orders O
   JOIN [Order Details] OD ON (O.OrderID = OD.OrderID)
   JOIN Customers C ON (C.CustomerID = O.CustomerID)
   GROUP BY 
      O.OrderID, C.CustomerID, C.CompanyName
)

And we can execute this, and restrict the rows with

SELECT * FROM dbo.fOrderSummary1()
WHERE OrderTotal BETWEEN 140 and 145
ORDER BY OrderTotal

With the same output. So nothing more than we had before. However, we can create a function that takes parameters. In this case we can pass the lower and upper bounds.

CREATE FUNCTION fOrderSummary2(@Lower int, @Upper int)
RETURNS TABLE
AS
RETURN(
   SELECT 
      O.OrderID, C.CustomerID, C.CompanyName,
      CONVERT(money,SUM((OD.UnitPrice * OD.Quantity) * (1-OD.Discount))) AS OrderTotal
   FROM Orders O
   JOIN [Order Details] OD ON (O.OrderID = OD.OrderID)
   JOIN Customers C ON (C.CustomerID = O.CustomerID)
   GROUP BY 
      O.OrderID, C.CustomerID, C.CompanyName
   HAVING CONVERT(money,SUM((OD.UnitPrice * OD.Quantity) * (1-OD.Discount))) 
          BETWEEN @Lower AND @Upper
)

Which we can now use as

SELECT * FROM dbo.fOrderSummary2(140, 145)
ORDER BY OrderTotal

Which removes the WHERE clause in the main SQL - and hides it away.

Rules for inline table-valued functions are similar as for a view - if the SELECT statement is updateable, then the function will also be updateable.