Great! Just as you can have multiple columns in your CTE, you can also have multiple CTEs, some of which may be recursive, others that may not. Take a look at the example below.
WITH Prime(X) AS (
Total (Multiply, Prime, Score) AS (
Multiply + 1,
X * (Multiply + 1)
WHERE Multiply < 3
ORDER BY Multiply ASC,
In the example, we first create a list of the first 5 prime numbers, and then we create a recursive CTE that calculates the multiples of these prime numbers.
After running and analyzing the query, you may see that the CTE
Total returns some duplicates. You may wonder why we haven't used
UNION. The problem is that SQL Server doesn't accept a
UNION between the anchor member and the recursive member in a CTE. The workaround for this problem is to
SELECT DISTINCT in the invocation part.