Need some recursive processing in TSQL (SQL Server 2005)?
Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write on top of my mind, sometimes you just want to make life easier and have a script you can just modify a bit to fit the new requirement. [more]
So what I usually use for recursions in SQL Server 2005 are Common Table Expressions (the "WITH" keyword). This is only available in SQL 2005 and very easy/efficient for recursive.
To read more about it check this link from MSDN : Using Common Table Expressions
And of course the ever helpful Books Online (search for maybe WITH or Common Table Expressions)
Nevertheless here's my basic examples to get "Descendants and Self" and "Ancestors and Self" information. In this case I only involved the ID, ParentID, and Sequence but you may add more columns (ID and ParentID are required to work). Also you might want to just retrieve the ID and just perform a JOIN afterwards.
11 — NOTE: replace these accordingly
12 — replace columns : MyEntityID, MyParentEntityID
13 — replace parameter : @MyEntityIDParameter
14 — replace table: MyTable
15 — also note that the condition for anchor and recursive part
16 — and the final consumption (select statement after the WITH block)
17 — can be changed dependending on requirement
18 WITH DescendantsAndSelf([ID], [ParentID], [Sequence]) AS
19 (
20 — self (anchor part)
21 SELECT
22 [ID] = MyEntityID,
23 [ParentID] = MyParentEntityID,
24 [Sequence] = 1
25 FROM MyTable
26 WHERE
27 MyEntityID = @MyEntityIDParameter
28
29 UNION ALL
30
31 — descendants
32 SELECT
33 [ID] = MyEntityID,
34 [ParentID] = MyParentEntityID,
35 [Sequence] = [Sequence] + 1
36 FROM DescendantsAndSelf AS CTE, MyTable AS T1
37 WHERE CTE.[ID] = T1.MyParentEntityID
38 )
39
40 SELECT *
41 FROM DescendantsAndSelf
Download above code as text file > CTE_descendantsAndSelf.txt (947.00 bytes)
11 — NOTE: replace these accordingly
12 — replace columns : MyEntityID, MyParentEntityID
13 — replace parameter : @MyEntityIDParameter
14 — replace table: MyTable
15 — also note that the condition for anchor and recursive part
16 — and the final consumption (select statement after the WITH block)
17 — can be changed dependending on requirement
18 WITH AncestorsAndSelf([ID], [ParentID], [Sequence]) AS
19 (
20 — self (anchor part)
21 SELECT
22 [ID] = MyEntityID,
23 [ParentID] = MyParentEntityID,
24 [Sequence] = 1
25 FROM MyTable
26 WHERE
27 MyEntityID = @MyEntityIDParameter
28
29 UNION ALL
30
31 — ancestors (recursive part)
32 SELECT
33 [ID] = MyEntityID,
34 [ParentID] = MyParentEntityID,
35 [Sequence] = [Sequence] + 1
36 FROM AncestorsAndSelf AS CTE, MyTable AS T1
37 WHERE CTE.[ParentID] = T1.MyEntityID
38 )
39
40 SELECT *
41 FROM AncestorsAndSelf
Download above code as text file > CTE_ancestorsAndSelf.txt (939.00 bytes)
Please let me know if I missed anything.
Hope you and me could find this useful someday.