Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 165

Using arguments in CTes

As with derived tables, you can also use arguments in the query used to define a CTE. Here’s an 

example.

DECLARE @empid AS INT = 3; 

 

WITH C AS 

( 

  SELECT YEAR(orderdate) AS orderyear, custid 

  FROM Sales.Orders 

  WHERE empid = @empid 

) 

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 

FROM C 

GROUP BY orderyear;

Defining Multiple CTEs

On the surface, the difference between derived tables and CTEs might seem to be merely semantic. 

However, the fact that you first define a CTE and then use it gives it several important advantages 

over derived tables. One of those advantages is that if you need to refer to one CTE from another, you 

don’t end up nesting them as you do with derived tables. Instead, you simply define multiple CTEs 

separated by commas under the same WITH statement. Each CTE can refer to all previously defined 

CTEs, and the outer query can refer to all CTEs. For example, the following code is the CTE alternative 

to the nested derived tables approach in Listing 5-2.

WITH C1 AS 

( 

  SELECT YEAR(orderdate) AS orderyear, custid 

  FROM Sales.Orders 

), 

C2 AS 

( 

  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 

  FROM C1 

  GROUP BY orderyear 

) 

SELECT orderyear, numcusts 

FROM C2 

WHERE numcusts > 70;

www.it-ebooks.info

---

166  

Microsoft SQL Server 2012 T-SQL Fundamentals

Because you define a CTE before you use it, you don’t end up nesting CTEs. Each CTE appears 

separately in the code in a modular manner. This modular approach substantially improves the read-

ability and maintainability of the code compared to the nested derived table approach.

Technically, you cannot nest CTEs, nor can you define a CTE within the parentheses of a derived 

table. However, nesting is a problematic practice; therefore, think of these restrictions as aids to code 

clarity rather than as obstacles.