Developing Matt

My Technical Journal

Common table expressions

leave a comment »

Instead of using temp tables use this

WITH myCTE (column1, column2, column3)
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

select * from myCTE

The only limitation that I know if is that you have to use the resultset immediately (since it is a function),

whereas a temp table is stored until the end of the procedure.

However, you can use multiple CTEs and separate them with a comma as illustrated by the following (found from here ):

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS





      (SELECT COUNT(1) FROM Products p

       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts

   FROM Categories c


ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS







   FROM Products p

   WHERE UnitPrice > 10.0


SELECT c.CategoryName, c.NumberOfProducts,

      p.ProductName, p.UnitPrice

FROM ProductsOverTenDollars p

   INNER JOIN CategoryAndNumberOfProducts c ON

      p.CategoryID = c.CategoryID

ORDER BY ProductName


Written by matt

February 4, 2008 at 11:27 am

Posted in Sql Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: