The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Can we generate sequence number witout Loop ? YES. Using Common Table Expression - Recursion (CTE Recursion)

When we execute the following script, It throws an error..

;

WITH CTEs

AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs

)

SELECT * FROM CTEs

GO

 

Error:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Normally, The default recursion limit in 100. OK

How to genarete upto 1000. Is there any option here ? YES

;

WITH CTEs

AS

(
SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs

)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)

GO

It generates 1 through 1000 with error when it exceeds the value(1000)

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1000 has been exhausted before statement completion.

Then, what would be the limit here ? The maximum limit is 32767 and default is 100

Ok. How to produce the sequence number (1 to 1000) without any ERROR using CTE Recursion

;

WITH CTEs

AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs WHERE [Number] <1000
)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)

GO 

Now, It generates the sequence number 1 through 1000 without any Error