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