0

When I execute the below query, it throws an error as

Msg 530, Level 16, State 1, Line 4 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

That too duplicate rows are displaying.

;WITH CTE_Entity AS
(
SELECT EH.EntityHierarchyId ,
       E.EntityId ,
       E.EntityName AS ClientName,
       E.UniqueEntityIdentifier AS ClientUniqueIdentifier,
       CAST(NULL AS VARCHAR(MAX))AS MemberName,
       CAST(NULL AS VARCHAR(MAX)) AS MemberUniqueIdentifier
FROM EntityHierarchy EH
INNER JOIN RefEntity E ON EH.EntityId  = E.EntityId
WHERE EH.ParentEntityId IS NULL

UNION ALL

SELECT CTE.EntityHierarchyId ,
       CTE.EntityId ,
       CTE.ClientName AS ClientName,
       CTE.ClientUniqueIdentifier AS ClientUniqueIdentifier,
       CAST(EE.EntityName AS VARCHAR(MAX))AS MemberName,
       CAST(EE.UniqueEntityIdentifier AS VARCHAR(MAX)) AS MemberUniqueIdentifier
FROM CTE_Entity CTE
INNER JOIN EntityHierarchy EHH ON CTE.EntityId = EHH.ParentEntityId
INNER JOIN RefEntity EE ON EHH.EntityId = EE.EntityId
)
SELECT  * FROM CTE_Entity

Any help will be appreciated.

enter image description here

Community
  • 1
  • 1
Yugandhar
  • 97
  • 9
  • Please, remove "union all " and use only "union" in sql qyery. – Vikram Jain Jan 24 '20 at 08:57
  • 1
    Hi, @jainvikram444 the above query is recursive CTE, we can not use UNION, for recursive we use only UNION AL – Yugandhar Jan 24 '20 at 09:00
  • @Yugandhar you have now added `OPTION (MAXRECURSION 0)` meaning that the error you state you get won't happen. If the problem you previously had doesn't exist any more, an d you have a new problem, you should post a new question. – Thom A Jan 24 '20 at 09:07
  • If you have so many levels, you should consider using `hierarchyid` instead of recursion. It's a *lot* faster as it onverts recursions to simple range searches. Removing the `MAXRECURSION` limit means your query can end up in an infinite recursion due to a simple error – Panagiotis Kanavos Jan 24 '20 at 09:52
  • `That too duplicate rows are displaying.` are you sure you aren't *already* seeing an infinite recursion? Relaxing `MAXRECURSION` won't fix that, it will only delay the error or just run forever until you're forced to kill the query – Panagiotis Kanavos Jan 24 '20 at 09:54

0 Answers0