Hi All

I have the below sql scripts that give me the error:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Ive tried using OPTION ( MAXRECURSION 0 ) or 1000 but it still  give me the same error.

WITH cte as (SELECT     ticketid, setstatus, setdate, row_number() OVER (partition BY (ticketid)
ORDER BY setdate ASC) AS rn
FROM         [OneForm].[dbo].[TFAM_SLA_Times]
UNION
SELECT     TicketID, 'Submitted', SubmittedON, 0
FROM         [OneForm].[dbo].vw_TFAM_ALL)

    SELECT     a.ticketid, SUM(OneForm.dbo.[CalcMinutesSLA_TFAM](a.setdate, b.setdate)) AS SLA_in_Min
     FROM         vw_TFAM_SLA_Combined AS a LEFT JOIN
                            vw_TFAM_SLA_Combined AS b ON a.ticketid = b.ticketid AND a.rn = b.rn - 1
     WHERE     (a.SetStatus = 'Submitted' OR
                            a.SetStatus = 'Acknowledged' OR
                            a.SetStatus = 'Pending')
                            
     GROUP BY a.TicketID

If i remove the call to the function [CalcMinutesSLA_TFAM] or the b.rn - 1 it works fine.

Here is the code for the function:

CREATE FUNCTION [dbo].[CalcMinutesSLA2] (
 @startTime DATETIME,
 @endTime DATETIME
 ) 
returns int
as
begin
   declare @minutes int;
   
   -- you should only need to alter these two to change work times
   declare @wdStartMins int, @wdLength int
   set @wdStartMins = 510; -- 8.5 hours from 00:00 or 8:30 am
   set @wdLength = 510; -- 8.5 hours (e.g., 8:30 am - 5:00 pm)

   declare @fwd datetime, @lwd datetime
   set @fwd = dateadd(mi, @wdStartMins, datediff(d, 0, @startTime));
   if (@fwd < @startTime) set @fwd = @startTime;
      
   set @lwd = dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, @endTime));
   if (@lwd > @endTime) set @lwd = @endTime;
   
   ;with cte(st, et)
   as
   (
      select @fwd
           , case datediff(d, @fwd, @lwd) 
                when 0 then @lwd 
                else dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, @fwd)) 
             end
      union all
      select dateadd(mi, @wdStartMins, datediff(d, 0, st+1))
           , case datediff(d, st+1, @lwd) 
                when 0 then @lwd 
                else dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, st+1)) 
             end
      from cte
      where dateadd(mi, @wdStartMins, datediff(d, 0, st+1)) < @lwd
   )
    select @minutes = sum(datediff(mi, st, et))
   from cte C LEFT JOIN web_holidays H ON CONVERT(varchar,C.st,101) = H.HolidayDate   
   where left(datename(dw, st), 1) <> 'S'
   and st < et
   and H.HolidayDate IS NULL
   ;
   
   return coalesce(@minutes, 0);
end