Write a program to generate Sequence number using RECURSIVE CTE LOOP

 Posted by Rajnilari2015 on 2/4/2016 | Category: Sql Server Interview questions | Views: 373 | Points: 40
Answer:

--*********** THE QUERY BEGINS ************************
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)

--*********** THE QUERY ENDS ************************

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.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response