Maximum recursion has been exhausted

Question

I need an efficient way to pass in a parameter [StartingNumber] and to count from [StartingNumber] sequentially until I find a number that is missing.

I use the following sql to get the next number:

      DECLARE @StartOffset int
        SET  @StartOffset = 23
        ; With Missing as (
            select @StartOffset as N where not exists(
    select * from [QUEUE] where QueueNum = @StartOffset AND ismelutash = 1)
        ), Sequence as 
    (       select @StartOffset as N from [QUEUE] where QueueNum = @StartOffset 
            union all
            select b.QueueNum from [QUEUE] b inner join Sequence s 
on b.QueueNum = s.N + 1 and b.ismelutash = 1
        )
        select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

It has been working for awhile but now when I run it I get 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

Anyone have any ideas? Thanks

EDIT:

I added maxrecursion but it just loads and doesn't return data:

DECLARE @StartOffset int
SET  @StartOffset = 50

DECLARE @isMelutash int
SET @isMelutash = 0


; With QueueFilters as (
    select queuenum from queue where ismelutash = 1
),  Missing as (
    select @StartOffset as N where not exists(select * from QueueFilters where queuenum = @StartOffset)
), Sequence as (
    select @StartOffset as N from QueueFilters  where queuenum = @StartOffset
    union all
    select b.queuenum from QueueFilters  b inner join Sequence s on b.queuenum = s.N + 1
)
select COALESCE((select N from Missing ),(select MAX(N)+1 from Sequence ) )
**OPTION(MAXRECURSION 150)**

gbn · Accepted Answer · 2011-03-06T19:43:16.740

You could use the MAXRECURSION option in your current code

However, there is no need to recurse line by line (using Itzik Ben-Gan's method). This will detect cases where there are no rows on Queue with ismelutash = 1 too because it uses the Tally table as a reference sequence

;WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
 select TOP 1
    Number 
 from 
    Tally T
    LEFT JOIN   --corrected, oops.
    [QUEUE] Q ON T.Number = Q.QueueNum AND Q.ismelutash = 1
 where
    T.Number >= @StartOffset AND T.Number <= 1000000
    AND
    Q.QueueNum IS NULL
 ORDER BY
    T.Number

Edit:

Original code with MAXRECURSION hint at the end. This link is better too

      DECLARE @StartOffset int
        SET  @StartOffset = 23
        ; With Missing as (
            select @StartOffset as N where not exists(
    select * from [QUEUE] where QueueNum = @StartOffset AND ismelutash = 1)
        ), Sequence as 
    (       select @StartOffset as N from [QUEUE] where QueueNum = @StartOffset 
            union all
            select b.QueueNum from [QUEUE] b inner join Sequence s 
on b.QueueNum = s.N + 1 and b.ismelutash = 1
        )
        select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))
        OPTION (MAXRECURSION 0)

Thanks a lot for helping me, one more question, I put in maxrecursion and it's loading indefinitely without returning data. If the recursion is 99 it's instant but 101 or 0, it takes forever. Any ideas? Also I'm not so advanced in sql, how would I implement the tally table? — Eitan, Mar 06 '11 at 08:47
It looks like there's an error in your code: Tally should be joined to Queue with a left join, not an inner join. Also, there's a syntax error: and is repeated. — Paul Keister, Mar 06 '11 at 19:24

Paul Keister · Answer 2 · 2011-03-06T08:25:12.870

2

I believe this is equivalent, and it doesn't use recursion:

    SELECT COALESCE(MIN(QueueNum)
        , (SELECT Max(QueueNum) + 1
            FROM [Queue] 
            WHERE QueueNum > @StartOffset)
        , @StartOffset)
    FROM [QUEUE]
    WHERE QueueNum >= @StartOffset
    AND ismelutash != 1

edited Mar 06 '11 at 08:25

answered Mar 06 '11 at 07:55

Paul Keister

12,851
5
46
75

Fails for at least one case: no rows in Queue > @StartOffset – gbn Mar 06 '11 at 08:04
OK, this is a bit trickier than I first thought, but I think there are really only 3 cases: simple gap in the queue, no gaps found, and @StartOffset > Max(QueueNum). The amended code matches the output of the original in all these cases. – Paul Keister Mar 06 '11 at 08:27
Which is why I used an external Tally table ;-) – gbn Mar 06 '11 at 08:37

Maximum recursion has been exhausted

2 Answers2

Linked