The maximum recursion 100 has been exhausted before statement completion. SQL Server

Question

I have this function to count with day names, and when I am add a long date he give this error
'The maximum recursion 100 has been exhausted before statement completion.'
Can someone please help?
CREATE FUNCTION COUNT
(@d1 DATE,
@d2 DATE,
@nd VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(WITH AllDates AS
(
SELECT @d1 AS DateOf
UNION ALL
SELECT DATEADD (DAY, 1, DateOf)
FROM AllDates
WHERE DateOf < @d2
)
SELECT COUNT(*) SumOfDays
FROM AllDates
WHERE EXISTS (SELECT 1
FROM STRING_SPLIT (@nd,' ')
WHERE DATENAME(weekday, dateof) = value)
)

Answer 1

You need to add the hint OPTION(MAXRECUSION 0) where you run the query. (You cannot put the hint inside the function.)

However, there are better techniques to achieve what you want to do, and I have an article on my web site that gives you suggestions: http://www.sommarskog.se/Short%20Stories/table-of-numbers.html

Answer 2

Hi @TAII EL MEHDI ,

The default number of iterations for recursive cte is 100. As long as this number is exceeded, the query will be interrupted and an error will be generated. If you want to remove this restriction, you can specify maxrecursion 0.

Please refer to:

   CREATE FUNCTION COUNT  
    (@d1 DATE,  
    @d2 DATE,  
    @nd VARCHAR(MAX))  
    RETURNS TABLE  
    AS  
    RETURN  
    (WITH AllDates AS  
    (  
    SELECT @d1 AS DateOf  
    UNION ALL  
    SELECT DATEADD (DAY, 1, DateOf)  
    FROM AllDates  
    WHERE DateOf < @d2  
    )  
    SELECT COUNT(*) SumOfDays  
    FROM AllDates  
    WHERE EXISTS (SELECT 1  
    FROM STRING_SPLIT (@nd,' ')  
    WHERE DATENAME(weekday, dateof) = value)  
    OPTION(MAXRECURSION 0)  
    )  

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Instead of trying to figure out how to make a recursive CTE work - you can use an inline tally approach to generate the list of dates between your start/end dates. Here is some sample code for you:

    Set Nocount On;

Declare @date1 date = '2020-01-01'
      , @date2 date = getdate()
      , @nd varchar(8000) = 'Monday Tuesday Saturday';

    Set STATISTICS io, time On;

 --==== Recursive Method
  Print 'Recursive Method';

   With AllDates As
        (
 Select @date1 As DateOf
  Union All
 Select dateadd(day, 1, DateOf)
   From AllDates
  Where DateOf < @date2
        )
 Select count(*) SumOfDays
   From AllDates
  Where Exists( Select 1
                  From STRING_SPLIT(@nd, ' ')
                 Where datename(weekday, dateof) = Value)
 Option (maxrecursion 0);

 --==== inline-tally version
  Print char(10) + 'Inline-Method';

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , dates (DateOf)
     As (
 Select Top (datediff(day, @date1, @date2) + 1) 
        dateadd(day, checksum(row_number() over(Order By @@spid)) - 1, @date1) 
   From t t1, t t2, t t3, t t4                  -- 10000 rows = over 27 years of possible dates
        )
 Select SumOfDays = count(d.DateOf)
   From dates                                    d
  Where Exists (Select *
                  From string_split(@nd, ' ')   ss
                 Where ss.value = datename(weekday, d.DateOf));

    Set STATISTICS io, time Off;

Answer 4

It also might be a better idea to change your @nd parameter to accept the numeric day of week instead of the week day name, and delimit that list by something other than a space (comma, semi-colon, pipe).

Using this calculation for the day of the week datediff(day, 0, DateOf) % 7 + 1 gives us Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7 (ISO day of week number).

Here is that code:

    Set Nocount On;

Declare @date1 date = '2020-01-01'
      , @date2 date = getdate()
      , @nd varchar(8000) = '1;2;6';

    Set STATISTICS io, time On;

 --==== Recursive Method
  Print 'Recursive Method';

   With AllDates As
        (
 Select @date1 As DateOf
  Union All
 Select dateadd(day, 1, DateOf)
   From AllDates
  Where DateOf < @date2
        )
 Select count(*) SumOfDays
   From AllDates
  Where Exists( Select 1
                  From STRING_SPLIT(@nd, ';')
                 Where datediff(day, 0, dateof) % 7 + 1 = Value)
 Option (maxrecursion 0);

 --==== inline-tally version
  Print char(10) + 'Inline-Method';

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , dates (DateOf)
     As (
 Select Top (datediff(day, @date1, @date2) + 1) 
        dateadd(day, checksum(row_number() over(Order By @@spid)) - 1, @date1) 
   From t t1, t t2, t t3, t t4                  -- 10000 rows = over 27 years of possible dates
        )
 Select SumOfDays = count(d.DateOf)
   From dates                                    d
  Where Exists (Select *
                  From string_split(@nd, ';')   ss
                 Where ss.value = datediff(day, 0, d.DateOf) % 7 + 1); -- datename(weekday, d.DateOf));

    Set STATISTICS io, time Off;