Styjun

I'm working with a stored procedure and using a CTE in SQL Server and I'm trying to reach some data from a 2 tables, but when the execution goes to the CTE query it gets an infinite loop and never ends, is there a way to prevent that infinite loop?

This is the query that I create:

WITH tableName(Id, enddate, statusDte, closeId, shceDte, calcDte, closeEndDte, ParentId, LastClose, lasCloseDte, closeClass,addSe,twon,code)
AS
(
 SELECT 
 tba.Id,
 CASE WHEN tb.ParentId IS NOT NULL 
 THEN tb.Id
 WHEN tb.statusDte IN (1,2,3) 
 THEN tb.calcDte ELSE tb.shceDte 
 END ForecastDueDate, 
 statusDte, closeId, shceDte, calcDte, 
 CASE WHEN tb.ParentId IS NULL 
 THEN closeEndDte ELSE NULL END, tb.ParentId, 0, 
 CASE WHEN tb.ParentId IS NOT NULL 
 THEN statusDte
 WHEN tb.statusDte = 5
 AND (tb.calcDte BETWEEN '1/1/2020 12:00:00 AM' AND '12/31/2020 11:59:59 PM' 
 OR tb.closeEndDte BETWEEN '1/1/2020 12:00:00 AM' AND '12/31/2020 11:59:59 PM') 
 THEN ams.GetPreviousNthFullAuditDate(tb.Id, tb.AuditID, 2) ELSE a.statusDate END as lastDate,
 a.closeClass, tba.addSe,tba.town,tba.code
 FROM 
 tableA tba 
 INNER JOIN 
 tableB tb ON tb.Id = tba.Id 
 WHERE 
 statusDte NOT IN (3,4) AND tba.IsAtve = 1

 UNION ALL

 SELECT 
 Id, enddate, 
 statusDte, statusDte, shceDte, calcDte, closeEndDte, ParentId,
 0, lasCloseDte, closeClass,addSe,twon,code
 FROM 
 tableName
 WHERE 
 enddate BETWEEN enddate AND '12/31/2020 11:59:59 PM'
)
SELECT * 
FROM tableName
OPTION (maxrecursion 0)

Expected results

Id enddate statusDte closeId shceDte calcDte closeEndDte parentId lastClose lastCloseDte closeClass addSe town code
----------- ----------------------- ------------- ----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------- ----------------------- ----------- --------------------------------- ---------------------- --------------------------------------------------
133 2011-04-04 00:00:00.000 22 14453 NULL 2011-04-04 00:00:00.000 2099-12-31 00:00:00.000 NULL 0 NULL 1 4707 EXECUTIVE DRIVE '' SAN DIEGO 123
56 2018-12-07 13:00:00.000 22 52354 NULL 2018-12-07 13:00:00.000 2019-12-07 00:00:00.000 NULL 0 NULL 1 75 STATE ST FL 24 '' BOSTON 345
12 2021-02-05 17:00:00.000 22 75751 NULL 2021-02-05 17:00:00.000 NULL NULL 0 NULL 1 1450 FRAZEE RD STE 308 '' SAN DIEGO 678
334 2019-03-07 16:30:00.000 15 66707 2019-03-07 16:30:00.000 2019-03-23 21:00:00.000 NULL NULL 0 2019-03-07 16:30:00.000 1 42690 RIO NEDO, STE E '' TEMECULA 91011
33 2020-01-10 17:00:00.000 22 65568 NULL 2020-01-10 17:00:00.000 NULL NULL 0 2018-01-10 17:00:00.000 1 2518 UNICORNIO ST. '' CARLSBAD 136
55 2020-04-16 20:00:00.000 22 67812 NULL 2020-04-16 20:00:00.000 NULL NULL 0 2018-04-17 20:00:00.000 1 4534 OSPREY STREET '' SAN DIEGO 653
66 2020-02-21 17:00:00.000 22 75956 NULL 2020-02-21 17:00:00.000 NULL NULL 0 2019-02-21 17:00:00.000 1 3511 CAMINO DEL RIO S, STE 305 '' SAN DIEGO 0484
094 2021-02-20 21:00:00.000 22 75629 NULL 2021-02-20 21:00:00.000 NULL NULL 0 NULL 1 29349 EAGLE DR '' MURRIETA 345

First, let's try to add some best practices. Qualify all your columns with the appropriate table alias. Just doing some of them is inconsistent and inconsistent style is difficult to read and prone to errors.

Next, you've (hopefully) dumbed down your actual query. Generic names like "tableA" hinder understanding.

Next - your first case expression seems highly suspicious. You have one branch returns tb.id and the others return what appears to be a date (or datetime). You can, unfortunately, cast an int to a datetime. Might not make any sense and it won't generate an error. So - does this make sense?

Next - you've made a common mistake with your datetime boundaries. Depending on your data you might never know this. But there is no reason to expect that and there is every reason to write your logic so that it avoids any possibility. Tibor discusses in great detail here. Shorter version - your upper boundary should always be an exclusive one to support all possible values of time for your datatype. 23:59:59 will ignore any time values with non-zero milliseconds. And use a literal format that is not dependent on language or connection settings.

Next, you add confusion. You named your columns in the cte declaration but your code also includes aliases for some (but not all - see, refer to the consistency comment) columns which differ significantly from the actual column name for the cte. The 2nd column for the cte is "enddate", the anchor query uses the alias "ForecastDueDate"

Next, you have this: tb.statusDte = 5. The name implies date; the literal implies something different. You have other columns that end in "Dte" that are obviously dates, but not this one? Danger, danger!

Next, you refer to columns "a.closeClass" and "a.statusDate". There is no table or alias named "a".

Lastly, you have:

WHERE enddate BETWEEN enddate AND '12/31/2020 11:59:59 PM'

Think about what you wrote. Is not enddate always between enddate and Dec 31 2010 (so long as enddate <= that value)? I think this is the source of your issue. You're not computing or adjusting anything from the anchor, so the recursed part just keeps selecting and selecting and selecting. There is no logic to end the recursion.

The next question is obviously "now to fix it". That is impossible to say without knowing your schema, what it represents, and your goal. The use of recursion here is not obvious.

If the data is in a structure that the hierarchy between records is is a loop then recursion goes to infinite causing a problem in SQL. You will see the resources used by SQL process is increasing tremendously.
If you use MAXRECURSION with a different value than 0 (zero lets SQL to continue recursion without a limit) you will be able to limit the recursion.
With data that is looping or referencing each other you can this MAXRECURSION parameter