user3299464 user3299464 - 2 months ago 10
SQL Question

TRANSACT SQL: "max recursion exhausted" error but good results for recursive query

The following recursive query should select the four Fridays after the current day:

DECLARE @friday date
DECLARE @today date

Set @friday = '2016-09-02'
Set @today = convert (date, getdate())

While (datediff(dd, @Friday, @today) % 7) != 0
BEGIN
Set @today = DATEADD (dd, 1, @today)
END;

With FourFridays (Friday) as (
Select @today
union all
select DATEADD(DD, 7, Friday)
FROM FourFridays
)

Select * from FourFridays
OPTION(MAXRECURSION 3)


The results I get look good, but I get a "maximum recursion 3 has been exhausted before statement completion" error in SQL Server Management Studio. Any ideas?

Answer

To preview where your recursion would lead without maxrecursion errors:

DECLARE @friday date
 DECLARE @today date

 Set @friday = '2016-09-02'
 Set @today = convert (date, getdate())

 While (datediff(dd, @Friday, @today) % 7) != 0
 BEGIN
 Set @today = DATEADD (dd, 1, @today) 
 END;

 With FourFridays (Friday, Recursion) as (
 Select @today, 1
 union all
 select DATEADD(DD, 7, Friday), Recursion + 1
 FROM FourFridays
 where  Recursion < 10
 )

 Select * from FourFridays 
 --OPTION(MAXRECURSION 3)
Comments