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
urir10
Member
93 Points
227 Posts
The statement terminated. The maximum recursion 100 has been exhausted before statement completio...
Aug 24, 2010 02:59 PM|LINK
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.
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.TicketIDIf 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