The basic CTE syntax is a WITH clause that
is used as a preface to a SELECT, INSERT, UPDATE or DELETE statement. It’s we
can use a temporary view; also we can use this for recursive traversal.
eg: I will just show how we can display 1
to 100 using CTE recursion
declare
@cnt int = 0;
WITH
DisplayNumberics(Nos)
as
(
select
@cnt
union all
select Nos+1 from
DisplayNumberics where Nos < 200
)
select
* from
DisplayNumberics
OPTION (MAXRECURSION 200)
By
default SQL Server will take the maximum recursion 100 times. If you want to
make it more we should use OPTION (MAXRECURSION value)
Please note for recursive implementation
the UNION ALL is important. A recursive function can be implemented using a special
form of CTE that combines two select statements with a UNION ALL. So it is
compulsory to have a top level UNION ALL operator for recursive common table
expressions. Also one ‘;’ (semicolon) is compulsory for SQL Server before
starting WITH clause, look at green highlight in the examples.
Another example: Cumulative sum
WITH
DisplayNumberics(Nos,CumulativeSum)
as
(
select 0,1
union all
select Nos+1,CumulativeSum + Nos from
DisplayNumberics where Nos < 200
)
select
* from
DisplayNumberics
OPTION (MAXRECURSION 200)