This is a fairly simple thing to do, but I had to look it up the other day and thought it would make a good blog. This is a good example of a blog that everyone should write to show they know a particular skill.
TL;DR: Use a comma between CTE definitions.
Let’s imagine that you need to combine a few queries together and the code is complex. You decide to use a CTE to make things easier. Suppose I have this query:
SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID WHERE ( SELECT COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID ) > 1 AND ( SELECT COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID ) > 1
That’s ugly, and I want to move the subqueries. I know this is slightly contrived, but imagine that what you really want is a couple views like:
CREATE VIEW BEACount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID CREATE VIEW PersonPhoneCount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID
However you don’t want to create views, what you really want is to make both of these queries CTEs and then call them from the original query.
I wasn’t sure how to do this, so I Googled. The first result was my friend, Pinal Dave’s blog. It took me about 10 seconds to read his explanation and apply it to my issue.
WITH BEACountCTE AS ( SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea GROUP BY bea.BusinessEntityID ) , PersonPhoneCountCTE AS ( SELECT pp.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp GROUP BY pp.BusinessEntityID ) SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID INNER JOIN PersonPhoneCountCTE pp ON pp.BusinessEntityID = p.BusinessEntityID INNER JOIN BEACountCTE bea ON bea.BusinessEntityID = p.BusinessEntityID WHERE BEA.cnt > 1 AND PP.cnt > 1
Note that at the top I have one WITH statement and both of my CTEs are separated by commas.
Easy enough, a quick thing to look up, and a handy item to know. If you are breaking up queries and using CTEs to make things easier to read, use a comma between your CTEs.
Filed under: Blog Tagged: syndicated, T-SQL 
