How do I iterate through hierarchical data in a Sql Server 2005 stored proc?
- by AlexChilcott
Hi,
I have a SqlServer2005 table "Group" similar to the following:
Id (PK, int)
Name (varchar(50))
ParentId (int)
where ParentId refers to another Id in the Group table. This is used to model hierarchical groups such as:
Group 1 (id = 1, parentid = null)
+--Group 2 (id = 2, parentid = 1)
+--Group 3 (id = 3, parentid = 1)
+--Group 4 (id = 4, parentid = 3)
Group 5 (id = 5, parentid = null)
+--Group 6 (id = 6, parentid = 5)
You get the picture
I have another table, let's call it "Data" for the sake of simplification, which looks something like:
Id (PK, int)
Key (varchar)
Value (varchar)
GroupId (FK, int)
Now, I am trying to write a stored proc which can get me the "Data" for a given group. For example, if I query for group 1, it returns me the Key-Value-Pairs from Data where groupId = 1. If I query for group 2, it returns the KVPs for groupId = 1, then unioned with those which have groupId = 2 (and duplicated keys are replaced).
Ideally, the sproc would also fail gracefully if there is a cycle (ie if group 1's parent is group 2 and group 2's parent is group 1)
Has anyone had experience in writing such a sproc, or know how this might be accomplished?
Thanks guys, much appreciated,
Alex
