Why this query is completed with error ?
;with tempData as
(
select 32 as col1, char(32) as col2
union all
select col1+1, char(col1+1) from tempData
)
select * from tempData
Asked
Active
Viewed 257 times
1
A-K
- 16,804
- 8
- 54
- 74
Jeevan Bhatt
- 5,881
- 18
- 54
- 82
-
-1 for not reading MSDN but still managing to blunder into an infinite loop – gbn Oct 11 '10 at 11:22
2 Answers
2
Recursion needs a terminating condition. For example
;with tempData as (
select 32 as col1, char(32) as col2
union all
select col1+1, char(col1+1) from tempData
where col1 < 255
)
select * from tempData
option (maxrecursion 223)
With regards to the question in the title about how it works internally see this answer.
Community
- 1
- 1
Martin Smith
- 438,706
- 87
- 741
- 845
-
- thanks for your answer its quit informative but now i want to know what is option(maxrecursion 0) ? – Jeevan Bhatt Oct 11 '10 at 11:15
-
@Jeevan - By default CTEs will stop recursion after 100 levels. `option(maxrecursion 0)` means that it will continue recursion indefinitely (for ever if you have an infinite loop - or at least until SSMS runs out of resources!). – Martin Smith Oct 11 '10 at 11:18
0
You have an infinite loop: where should it end?
gbn
- 422,506
- 82
- 585
- 676
-
You are right its infinit loop but i want to know how its loop because i dont know how 'With' works. – Jeevan Bhatt Oct 11 '10 at 11:13