The maximum recursion 100 has been exhausted before statement completion
Specify the maxrecursion option at the end of the query:
...from EmployeeTreeoption (maxrecursion 0)That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.
it is just a sample to avoid max recursion error. we have to use option (maxrecursion 365); or option (maxrecursion 0);
DECLARE @STARTDATE datetime; DECLARE @EntDt datetime; set @STARTDATE = '01/01/2009'; set @EntDt = '12/31/2009'; declare @dcnt int; ;with DateList as ( select @STARTDATE DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) ) select count(*) as DayCnt from ( select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' ) )aoption (maxrecursion 365);