The maximum recursion 100 has been exhausted before statement completion.
In this article, I am going to explain how to use cte.if we use cte to display the 200 records,we will get the below Error.Here i will explain,how to over come this error.
"The maximum recursion 100 has been exhausted before statement completion."
Dear Friends,
How to Overcome the below error in sql.here i am going to explain it.
"The maximum recursion 100 has been exhausted
in my example,i want to display the date from today date to '2012/12/2'WITH CTE AS
(
SELECT DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE())[FIRST SUNDAY DATE],DATENAME(DW,DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE()))[DAY NAME]
UNION ALL
SELECT DATEADD(D,1,[FIRST SUNDAY DATE]),DATENAME(DW,DATEADD(D,1,[FIRST SUNDAY DATE]))FROM CTE WHERE [FIRST SUNDAY DATE]<='2012/12/2'
)
SELECT *FROM CTE
That time i will get the below error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
But i got an solution to display the date.Just put the below line in the above coding.
option(maxrecursion 0);
please add the above line to the coding and run it.
WITH CTE AS
(
SELECT DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE())[FIRST SUNDAY DATE],DATENAME(DW,DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE()))[DAY NAME]
UNION ALL
SELECT DATEADD(D,1,[FIRST SUNDAY DATE]),DATENAME(DW,DATEADD(D,1,[FIRST SUNDAY DATE]))FROM CTE WHERE [FIRST SUNDAY DATE]<='2012/12/2'
)
SELECT *FROM CTE
option(maxrecursion 2000);
2000 is not an static value.
Thank you For Reading this article.Dont Forget to say your comments here.
Thank you.
Note:
I hope this will be useful to all. If you have queries please contact the below mail address:
Reference: Rathin813.blogspot.com