Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 177

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 by using DML statements, 

the same way you query other tables. If the function accepts input parameters, you specify those in 

parentheses following the function’s name. Also, make sure you provide an alias for the table expres-

sion. Providing a table expression with an alias is not always a requirement, but it 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.GetCustOrders(1) AS O;

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 O.orderid, O.custid, OD.productid, OD.qty 

FROM dbo.GetCustOrders(1) AS O 

  JOIN Sales.OrderDetails AS OD 

    ON O.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

www.it-ebooks.info

---

178  

Microsoft SQL Server 2012 T-SQL Fundamentals

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

IF OBJECT_ID('dbo.GetCustOrders') IS NOT NULL 

  DROP FUNCTION dbo.GetCustOrders;