EXAMPLE OF RECURSIVE Common Type Expression ( CTE )

A Common Table Expression (CTE) is a temporary, named result set.
Once created, a CTE can then be used in subsequent statements in the
same SQL batch.
This provides two benefits. First, it allows queries to be written in a
more clear and concise manner. Second, because CTEs can refer to
themselves,
they allow the creation of recursive queries. In previous versions of
SQL Server, stored procedures could call themselves, but SQL statements
could not.
CTEs therefore represent a new and powerful addition to the T-SQL language.

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query.
We can use CTE as Recursive CTE (Common Table Expression). Following
example shows how we can use recursive CTE. Here we are creating list
of numbers.

WITH NumberList(Num) as
(
SELECT 1
UNION ALL
SELECT Num + 1
FROM NumberList
WHERE Num < 1000
)
SELECT TOP 50 * FROM NumberList

Pawan Kumar
Pawankkmr@hotmail.com