SQL Get Parent And Subordinate Groups
Join the DZone community and get the full member experience.
Join For Free
CREATE FUNCTION dbo.fn_Get_All_Subordinate_And_Parent_Groups
(
@intGroupId int
)
RETURNS @tblGroups Table (intGroupId int)
AS
BEGIN
INSERT INTO @tblGroups
values (@intGroupId)
WHILE EXISTS ( SELECT nUserGroup_ID
FROM tblUserGroups
WHERE intParentId In (SELECT intGroupId FROM @tblGroups)
AND nUserGroup_ID NOT IN (SELECT intGroupId FROM @tblGroups)
)
BEGIN
INSERT INTO @tblGroups
SELECT nUserGroup_ID
FROM tblUserGroups
WHERE intParentId In (SELECT intGroupId FROM @tblGroups)
AND nUserGroup_ID NOT IN (SELECT intGroupId FROM @tblGroups)
END
WHILE EXISTS ( SELECT nUserGroup_ID
FROM tblUserGroups
WHERE nUserGroup_ID In (
SELECT intParentId
FROM tblUserGroups
WHERE nUserGroup_ID in (SELECT * FROM @tblGroups)
)
AND nUserGroup_ID NOT IN (SELECT * FROM @tblGroups)
)
BEGIN
INSERT INTO @tblGroups
SELECT nUserGroup_ID
FROM tblUserGroups
WHERE nUserGroup_ID In (
SELECT intParentId
FROM tblUserGroups
WHERE nUserGroup_ID in (SELECT * FROM @tblGroups)
)
AND nUserGroup_ID NOT IN (SELECT * FROM @tblGroups)
END
--REMOVE GROUP ID 1 (TOP LEVEL GROUP)
DELETE FROM @tblGroups WHERE intGroupId = 1
RETURN
END
sql
Opinions expressed by DZone contributors are their own.
Comments