Saturday, August 22, 2009

Recursive Common Table Expressions - SQL

One of the best use of the recursive common table expressions in SQL is to generate consecutive numbers within a range from very simple SQL query.

The query below will generate numbers 1 to 100 from a Select statement using recursive common table expression.

;WITH CTE AS
(
SELECT 1 Number
UNION ALL
SELECT Number + 1
FROM CTE
WHERE Number
)
SELECT Number
FROM CTE