Table Expressions in Microsoft SQL Server 2008 T-SQL

Inline Table-Valued Functions

Inline TVFs are reusable table expressions that support input parameters. In all respects except for the support for input parameters, inline TVFs are similar to views. For this reason, I like to think of inline TVFs as parameterized views, even though they are not called this formally.

For example, the following code creates an inline TVF called fn_GetCustOrders in the TSQLFundamentals2008 database:

USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
  DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT orderid, custid, empid, orderdate, requireddate,
    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
    shipregion, shippostalcode, shipcountry
  FROM Sales.Orders
  WHERE custid = @cid;
GO

This inline TVF accepts an input parameter called @cid representing a customer ID, and returns all orders that were placed by the input customer. You query inline TVFs like you query other tables with DML statements. If the function accepts input parameters, you specify those in parentheses following the function’s name. Also, make sure you provide an alias to the table expression. Providing a table expression with an alias is not always a requirement but is a good practice because it makes your code more readable and less prone to errors. For example, the following code queries the function requesting all orders that were placed by customer 1:

SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;

This code returns the following output:

orderid     custid
----------- -----------
10643       1
10692       1
10702       1
10835       1
10952       1
11011       1

As with other tables, you can refer to an inline TVF as part of a join. For example, the following query joins the inline TVF returning customer 1’s orders with the Sales.OrderDetails table, matching customer 1’s orders with the related order lines:

SELECT CO.orderid, CO.custid, OD.productid, OD.qty
FROM dbo.fn_GetCustOrders(1) AS CO
  JOIN Sales.OrderDetails AS OD
    ON CO.orderid = OD.orderid;

This code returns the following output:

orderid     custid      productid   qty
----------- ----------- ----------- ------
10643       1           28           15
10643       1           39           21
10643       1           46            2
10692       1           63           20
10702       1            3            6
10702       1           76           15
10835       1           59           15
10835       1           77            2
10952       1            6           16
10952       1           28            2
11011       1           58           40
11011       1           71           20

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
  DROP FUNCTION dbo.fn_GetCustOrders;