I can see the use of this CTE in many places. Since I don't have the sql 2005 installed, is it possible to use variables inside the CTEs? The below example is for illustration purpose only and of no practical use i think of:
declare @maxcount as int
set @maxcount=10
WITH TitleCount (authorID, titleCount) AS
(
SELECT au_id, COUNT(title_id)
FROM titleauthor
GROUP BY au_id having count(title_id) <= @maxcount
)
SELECT au_id, au_lname, au_fname, titleCount
FROM authors a
INNER JOIN TitleCount
ON TitleCount.authorID = a.au_id
Thanks