Sequence number generation using RECURSIVE CTE LOOP in SQL Server

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 377
The below program will do so

DECLARE @maxLimit INT = 1000000

;WITH NumCTE AS(
SELECT Rn = 1
UNION ALL
SELECT Rn+1
FROM NumCTE WHERE Rn < @maxLimit)
SELECT *
FROM NumCTE
OPTION(MAXRECURSION 0)


In the above query, we are starting with number 1 and then in the recursive part the CTE moves on going till it reaches the @maxLimit. And at every step, the merging of the values is happening through the UNION ALL.

Finally the result is displayed outside the CTE.

Comments or Responses

Login to post response