Today I am going to show a very simple example of CTE recursion.. For CTE definition, please refer MSDN.
Suppose we want to generate 1 to 10 with CTE. Then we can use the below code:
;WITH cte AS
SELECT num+1 AS num FROM cte WHERE num+1 <=10
SELECT * FROM cte