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