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


Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: