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 254 times
1
A-K
- 16,510
- 7
- 52
- 71
Jeevan Bhatt
- 5,631
- 16
- 51
- 81
-
-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
- 419,657
- 83
- 708
- 800
-
- 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
- 408,740
- 77
- 567
- 659
-
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