-2

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 TEST
    (@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)
        )
taii el mehdi
  • 41
  • 7
  • 1
    I don't think it's permitted to name a function COUNT. It's a reserved word – SteveC Sep 23 '20 at 19:36
  • 2
    @taiielmehdi What `@d1, @d2, @nd` values did you supply to the query? – Radagast Sep 23 '20 at 19:51
  • @taii-el-mehdi Please post some data that you used for input parameters. – Jeff Moden Sep 23 '20 at 21:43
  • @JeffModen SELECT * FROM [dbo].[COUNT]('2022-06-06','2029-08-08','Monday Tuesday') – taii el mehdi Sep 23 '20 at 21:48
  • @SteveC i am using another name but without any success :) – taii el mehdi Sep 23 '20 at 21:49
  • As for the recursion error, you need to set max recursion in the query that's calling the function. If poor CPU performance and a totally unnecessary number of reads don't deter you from using incremental recursive CTEs, this reason alone should. Please see the following answer on SO. https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232 – Jeff Moden Sep 23 '20 at 21:51
  • SELECT * FROM [dbo].[COUNT_dt]('2022-06-06','2029-08-08','Monday Tuesday') return 750. My function appears to be working properly – SteveC Sep 23 '20 at 21:52
  • @taii-el-mehdi So post what YOUR using! – Jeff Moden Sep 23 '20 at 21:52
  • I changed the name of the function to Count_dt instead of Count. It's not good use to name a function with a reserved literal so I added _dt to the name – SteveC Sep 23 '20 at 21:56
  • @SteveC i am try but she give me this error "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – taii el mehdi Sep 23 '20 at 21:56
  • @SteveC Is Just a fake name – taii el mehdi Sep 23 '20 at 21:57
  • With the method I'm suggesting there is no maximum recursion. The tally function will completely fill a UNIQUE BIGINT – SteveC Sep 23 '20 at 22:00
  • 1
    Like I said, you need to add the MAXRECURSION option to your outer query. SELECT * FROM [dbo].[COUNT]('2022-06-06','2029-08-08','Monday Tuesday') OPTION (MAXRECURSION 0) – Jeff Moden Sep 23 '20 at 22:29
  • I updated my answer – SteveC Sep 23 '20 at 22:44

2 Answers2

0

Just add option (maxrecursion 0):

SELECT COUNT(*) SumOfDays 
FROM AllDates 
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(@nd,' ') 
              WHERE DATENAME(weekday,dateof) = value
             )
OPTION (MAXRECURSION 0)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @taiielmehdi . . . That is not a helpful comment. You should specify what the error is. – Gordon Linoff Sep 23 '20 at 21:54
  • The error was probably because you used the "AllDates" table, which the OP probably doesn't have. Please post how to construct your "AllDates" table. – Jeff Moden Sep 23 '20 at 23:02
  • @JeffModen . . . `AllDates` is the CTE that the OP has defined. I didn't include that in the answer. – Gordon Linoff Sep 23 '20 at 23:19
  • @GordonLinoff Interesting. That may be what the OP didn't understand either. I do, however, agree that this OP really needs to get in the habit of posting what error they got. Thanks for the feedback, Gordon. – Jeff Moden Sep 24 '20 at 02:17
0

Because the function doesn't reference any physical tables it's good to specify 'with schemabinding' when defining the function. This allows the optimizer to skip late binding to objects. Here's the function

CREATE FUNCTION TEST
    (@d1 DATE, 
     @d2 DATE, 
     @nd VARCHAR(MAX))
RETURNS TABLE with schemabinding
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)
        )

Query to execute

SELECT * 
FROM [dbo].[TEST]('2022-06-06','2029-08-08','Monday Tuesday') 
OPTION (MAXRECURSION 0);

Output

SumOfDays
750

Below is a different approach using a tally function

Tally based approach

One way to go would be to drop the recursive cte altogether and go with a "tally" table approach. Please see this link for more details.

Something like this

Inline table valued function

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

Query

CREATE FUNCTION COUNT_Dt(@d1 DATE, @d2 DATE, @nd VARCHAR(MAX))
RETURNS TABLE with schemabinding
AS
RETURN
select COUNT(*) SumOfDays 
FROM dbo.fnTally(0, datediff(d, @d1, @d2)) fn
        cross apply
        (select dateadd(d, fn.n, @d1) dt) da
WHERE EXISTS(SELECT 1
             FROM STRING_SPLIT(@nd,' ') 
             WHERE DATENAME(weekday,da.dt)=[value]);
go
SteveC
  • 5,955
  • 2
  • 11
  • 24