The maximum recursion 100 has been exhausted before statement completions

Question

;WITH UserSequence AS
(
    SELECT 
        LastEnrolledOn, Users, ROW_NUMBER() OVER(ORDER BY LastEnrolledOn) as 'Sequence'
    FROM 
        (select  sum(ct) users, LastEnrolledOn from (select  count(*) as ct,LastEnrolledOn from (select  serialnumber, LastEnrolledOn ,count(*) over (partition by [lastenrolledon]) as total 
FROM Devices) devices group by total,LastEnrolledOn )
 devciss group by LastEnrolledOn) WS1enroll
),
UserValues AS
(
  SELECT  
     u.Users AS 'UserValue', u.LastEnrolledOn, u.Sequence
  FROM UserSequence u
  WHERE Sequence = 1

  UNION ALL 

  SELECT  
     u.Users + uv.UserValue AS 'UserValue', u.LastEnrolledOn, u.Sequence
  FROM UserSequence u
  INNER JOIN UserValues uv ON u.Sequence = uv.Sequence + 1
)
SELECT
    LastEnrolledOn, Sequence, UserValue AS 'Users'
FROM 
    UserValues
ORDER BY 
    Sequence

I have devices table having data from almost 2013 to 2021 but not every date between. I need count total enrollment of every row with adding to next row total. Query is working fine for few dates.

When executing whole table getting.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Does this answer your question? [The maximum recursion 100 has been exhausted before statement completion](https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion) — Luuk, Jul 08 '21 at 16:47
I suppose the real question is, do you *need* recursion? You *seem* to be describing cumulative count/sum, which doesn't require it. — Thom A, Jul 08 '21 at 16:48
Also, pet peeve, the semicolon (`;`) is a **statement terminator**; it goes at the *end* of every statement, not the start of statements that require the **previous statement** to be properly terminated. — Thom A, Jul 08 '21 at 16:49

score 0 · Answer 1 · answered Jul 08 '21 at 16:50

You can use option(MAXRECURSION n). Here n is any value between 0 and 32,767. 0 means no limit but 32,767. I have added option(maxrecursion 1000) into your query.

;WITH UserSequence AS
(
    SELECT 
        LastEnrolledOn, Users, ROW_NUMBER() OVER(ORDER BY LastEnrolledOn) as 'Sequence'
    FROM 
        (select  sum(ct) users, LastEnrolledOn from (select  count(*) as ct,LastEnrolledOn from (select  serialnumber, LastEnrolledOn ,count(*) over (partition by [lastenrolledon]) as total 
FROM Devices) devices group by total,LastEnrolledOn )
 devciss group by LastEnrolledOn) WS1enroll
),
UserValues AS
(
  SELECT  
     u.Users AS 'UserValue', u.LastEnrolledOn, u.Sequence
  FROM UserSequence u
  WHERE Sequence = 1

  UNION ALL 

  SELECT  
     u.Users + uv.UserValue AS 'UserValue', u.LastEnrolledOn, u.Sequence
  FROM UserSequence u
  INNER JOIN UserValues uv ON u.Sequence = uv.Sequence + 1
)
SELECT
    LastEnrolledOn, Sequence, UserValue AS 'Users'
FROM 
    UserValues
ORDER BY 
    Sequence
OPTION (MAXRECURSION 1000)

The maximum recursion 100 has been exhausted before statement completions

1 Answers1