DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

SQL Get Parent And Subordinate Groups

01.12.2007

| 568 views |

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

Tags: