I agree on readability, which is very important for maintaining code for developers.

One item I have found is that in certain situations referencing a CTE over and over again some how slows down the query. I have found that the calls to the cteFilteredSearchList are slower then going to the table directly dbo.SearchList or a view. Just what I have found from using CTEs for search results.

Example:

;with cteFilteredSearchList

as

(

Select....from dbo.SearchList where Active='1'

),

cteLookupByX

as

(

select .... from cteFilteredSearchList where ....

)

cteLookupByY

as

(

select .... from cteFilteredSearchList where ....

)

Then union the results and review what came back.