CODEWITHSUNDEEP
sqlsql-serverrecursion
user3299464
user3299464

Reputation: 17

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?

Upvotes: 0

Views: 553

Answers (1)

Manuela Re
Manuela Re

Reputation: 41

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)

Upvotes: 1

Related Questions