quote:

---

Originally posted by sadiqmodan

Hello,
I have data as like below

mainID parentid VirtualID Text
====== ======== ========= ====
1 0 5 Node1
2 1 0 Node1.1
3 1 0 Node1.2
4 0 9 Node2
5 4 0 Node2.1
6 4 0 Node2.2
7 0 0 Node3
8 7 6 Node3.1
9 7 0 Node3.2

mainId: means NodeId
VirtualID: 0 means Real node, >0 meand virtual node
e.g. first record represent Node1 have no parent and it is virtual of the Node2.1

when I Delete Node2.1 then I have to delete below records.
mainID parentid VirtualID Text
====== ======== ========= ====
1 0 5 Node1
2 1 0 Node1.1
3 1 0 Node1.2
5 4 0 Node2.1

because Node1 is virtual of Node2.1 and Node1 have 2 child. for this I have Implemented below query.

================================================
declare @tmp int = 5
;WITH CTE
AS
(
SELECT parentid, mainid, virtualid, LVL = RIGHT('000' + CONVERT(varchar(MAX), mainid), 3)
from tblTree where mainid = @tmp or virtualid = @tmp
UNION ALL
SELECT tblTree.parentid, tblTree.mainid, tblTree.virtualid, LVL = CTE.lvl + RIGHT('000' + CONVERT(varchar(MAX), tblTree.mainid), 3) from CTE
inner join tblTree on
(tblTree.parentid = CTE.mainid) OR (tblTree.mainid = CTE.virtualid and tblTree.parentid = CTE.virtualid)

), CTE2
AS
(
SELECT tblTree.parentid, tblTree.mainid, tblTree.virtualid, LVL = RIGHT('000' + CONVERT(varchar(MAX), tblTree.mainid), 3)
from tblTree inner join CTE on tblTree.virtualid = CTE.mainid
UNION ALL
SELECT tblTree.parentid, tblTree.mainid, tblTree.virtualid, LVL = CTE2.lvl + RIGHT('000' + CONVERT(varchar(MAX), tblTree.mainid), 3) from tblTree
inner join CTE2 on
(tblTree.parentid = CTE2.mainid or tblTree.virtualid = CTE2.mainid) AND CTE2.mainID <> @tmp)

select distinct mainid, parentid, virtualid, LVL from CTE
union all
select distinct mainid, parentid, virtualid, LVL from CTE2
================================================

but when I have record as below
mainID parentid VirtualID
1 0 5
2 1 0
3 1 0
4 0 9
5 4 0
6 4 0
7 0 12
8 7 6
9 7 0
10 8 0 Node3.1.1
11 8 0 Node3.1.2
12 10 0 Node3.1.2.1

and @tmp = 10 then I get the error like

----------------------------------------------
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
----------------------------------------------
at the end of query I have added below line
option (maxrecursion 5000)
but still it will gives error.

So please can any have advance solution.

Thanks in Advance

---