CREATE TABLE [dbo].[groupsAssociation](
	[Id] [int] NOT NULL,
	[GroupId] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [varchar](250) NULL,
	[ParentGroupId] [bigint] NULL,
	[ParentGroupName] [varchar](250) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[groupsAssociation]
           ([GroupId]
           ,[GroupName]
           ,[ParentGroupId]
           ,[ParentGroupName])
SELECT 269, 'B1', 271, 'B'
SELECT 270, 'B2', 271, 'B'
SELECT 269, 'B1', 272, 'A'
SELECT 272, 'A', 271, 'B'
SELECT 271, 'B', 272, 'A'
SELECT 273, 'C', NULL, NULL
SELECT 274, 'D', NULL, NULL

-- First Function
CREATE FUNCTION [dbo].[GetAllSubGroups]         
(        
 -- Add the parameters for the function here        
 @parentGroupId varchar(50)      
)  
RETURNS varchar(4000)  
AS        
BEGIN        
      
  DECLARE @finalGroups varchar(8000)        
  DECLARE @parentGroupIdCopy varchar(50)      
  DECLARE @Pos int      
  DECLARE @Token varchar(50)      
  DECLARE @subGroups varchar(8000)       
  DECLARE @ShouldDo bit      
  DECLARE @cnt int  
  set @cnt = 0  
  --set @parentGroupId = '62'      
      
  set @parentGroupIdCopy = @parentGroupId      
      
  SET @parentGroupId = LTRIM(RTRIM(@parentGroupId))+ '#'      
  SET @Pos = CHARINDEX('#', @parentGroupId, 1)      
  set @Token = ''      
  set @ShouldDo = 1      
  set @finalGroups = ''      
-----------------------------------      
  DECLARE @TempTable table ([GroupId] int NOT NULL)  
  DECLARE @RemainingGroups table ([GroupId] int NOT NULL)  
  IF REPLACE(@parentGroupId, '#', '') <> ''      
  BEGIN   
  insert into @TempTable ([GroupId])  
   select convert(int, @parentGroupIdCopy)  
  -- select Id, groups from @TempTable  
   WHILE @Pos > 0      
   BEGIN      
    SET @Token = LTRIM(RTRIM(LEFT(@parentGroupId, @Pos - 1)))      
    IF @Token <> ''      
     BEGIN      
      set @ShouldDo = 1 --reset it here.      
      WHILE @ShouldDo > 0      
       BEGIN      
        set @subGroups = [dbo].[GetSubGroups] (@Token)  
        set @subGroups = LTRIM(RTRIM(@subGroups))  
        -- Add groups one by one here   
        set @cnt = @cnt + 1  
  
        insert into @TempTable ([GroupId])  
        select convert(int, ColumnData) from fn_CSVToTable(@subGroups) where ColumnData not in (select [GroupId] from @TempTable)  
        --insert into @rtnTable select * from @TempTable  
        --if (@cnt = 5)  
  -- return   
        --set @subGroups =   
        if Len(IsNull(@subGroups,'')) > 0      
         begin  
          set @finalGroups = @subGroups +  ',' + @finalGroups   
          -- exclude those groups that are already we traversed/retrived  
          insert into @RemainingGroups ([GroupId])  
           select convert(int, ColumnData) from fn_CSVToTable(@subGroups) where ColumnData not in (select [GroupId] from @TempTable)  
           set @subGroups = (  
    select Left(IGroups,Len(IGroups)-1) [IGroups] from   
    (SELECT TOP 1   
      ( SELECT convert(varchar (10), GroupId) + ','  
       FROM @RemainingGroups d2 ORDER BY 1  
     FOR XML PATH('') ) AS  [IGroups]  
     FROM @RemainingGroups) as d1)  
          set @Token = [dbo].[GetSubGroups] (@subGroups)   
          --return @subGroups  
         end      
        else      
         begin      
           set @finalGroups = IsNull(@Token,'') +  ',' + @finalGroups       
           SET @ShouldDo = 0        
         end      
       END      
     END      
      
    SET @parentGroupId = RIGHT(@parentGroupId, LEN(@parentGroupId) - @Pos)      
    SET @Pos = CHARINDEX('#', @parentGroupId, 1)      
   END      
  END      
      
  set @finalGroups = @finalGroups + LTRIM(RTRIM(@parentGroupIdCopy))      
      
set @finalGroups = (  
    select Left(IGroups,Len(IGroups)-1) [IGroups] from   
    (SELECT TOP 1   
      ( SELECT distinct convert(varchar (10), GroupId) + ','  
       FROM @TempTable d2 ORDER BY 1  
     FOR XML PATH('') ) AS  [IGroups]  
     FROM @TempTable) as d1)  
      
RETURN @finalGroups      

END        
GO
-- Second function 
Create FUNCTION [dbo].[GetAllSubGroupsByList]         
(        
 -- Add the parameters for the function here        
 @GroupIdsList varchar(500)     
)  
RETURNS varchar(4000)  
AS     
BEGIN
-- select dbo.[GetAllSubGroupsByList]('271')

	Declare @str varchar(4000)   
	DECLARE @GroupsTable table ([GroupId] int NOT NULL) 
	insert into @GroupsTable ([GroupId]) 
	select Id from [Group] where id in (select convert(int, ColumnData) from fn_CSVToTable(@GroupIdsList))
	DECLARE @GroupsTableB table ([GroupIds] varchar(500))
	insert into  @GroupsTableB ([GroupIds]) 
	select dbo.GetAllSubGroups(GroupId) from @GroupsTable
--	set @str = (
--SELECT DISTINCT STUFF( (SELECT distinct convert(varchar (4000),GroupIds) + ',' from @GroupsTableB
-- FOR XML PATH('')),1,1,'') as GroupIds
--FROM @GroupsTableB)
set @str = (
    select Left(IGroups,Len(IGroups)-1) [IGroups] from   
    (SELECT TOP 1   
      ( SELECT distinct convert(varchar (4000), GroupIds) + ','  
       FROM @GroupsTableB d2 ORDER BY 1  
     FOR XML PATH('') ) AS  [IGroups]  
     FROM @GroupsTableB) as d1) 

return @str
END

-- helper functions
CREATE FUNCTION [dbo].[GetSubGroups]     
(    
 -- Add the parameters for the function here    
 @parentGroupIdList nvarchar(500)  
)    
RETURNS nvarchar(4000)    
AS    
BEGIN    
  
DECLARE @allSubGroups nvarchar(4000)    
   
SELECT @allSubGroups =   
 (CASE WHEN @allSubGroups IS NULL   
  THEN Convert(nvarchar(10),GroupId)   
 ELSE   
  @allSubGroups + ',' + Convert(nvarchar(10),GroupId)   
 END)  
  
FROM dbo.GroupsAssociation where ParentGroupId IN (SELECT ColumnData FROM [dbo].[fn_CSVToTable] (@parentGroupIdList))   
  
RETURN @allSubGroups  
  
END
GO
CREATE Function [dbo].[fn_CSVToTable] (@CSVList Varchar(3000))          
Returns @Table Table (ColumnData int)          
As          
Begin          
If right(@CSVList, 1) <> ','          
Select @CSVList = @CSVList + ','          
          
Declare @Pos Smallint,          
@OldPos Smallint          
Select @Pos = 1,          
@OldPos = 1          
          
While @Pos < Len(@CSVList)          
Begin          
Select @Pos = CharIndex(',', @CSVList, @OldPos)          
Insert into @Table          
Select cast(LTrim(RTrim(SubString(@CSVList, @OldPos, @Pos - @OldPos))) as int) Col001          
Select @OldPos = @Pos + 1          
End          
          
Return          
End   
  

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

BEGIN TRAN

CREATE TABLE [dbo].[groupsAssociation](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [GroupId] [int] NOT NULL,
        [GroupName] [varchar](250) NULL,
        [ParentGroupId] [bigint] NULL,
        [ParentGroupName] [varchar](250) NULL
) ON [PRIMARY]

--Fixed sample data -> you missed "UNION ALL"
INSERT INTO [dbo].[groupsAssociation]
           ([GroupId]
           ,[GroupName]
           ,[ParentGroupId]
           ,[ParentGroupName])
SELECT 269, 'B1', 271, 'B' UNION ALL
SELECT 270, 'B2', 271, 'B' UNION ALL
SELECT 269, 'B1', 272, 'A' UNION ALL
SELECT 272, 'A', 271, 'B' UNION ALL
SELECT 271, 'B', 272, 'A' UNION ALL
SELECT 273, 'C', NULL, NULL UNION ALL
SELECT 274, 'D', NULL, NULL

--Actual query
SELECT GroupId, Groups
FROM (SELECT GroupId,  
      GroupName + ISNULL(',' + STUFF((SELECT ',' + GroupName 
                                      FROM (SELECT children.GroupName, nodes.Id
                                            FROM [groupsAssociation] nodes 
                                            --Get child ID
                                            OUTER APPLY (SELECT [ParentGroupId], [GroupId] AS childid, [GroupName]
                                                         FROM [groupsAssociation] st 
                                                         WHERE nodes.[GroupId] = st.[ParentGroupId]) children) t2
                                      WHERE t2.Id = t1.Id
                                      FOR XML PATH('')), 1, 1, ''),'') AS Groups
      FROM [groupsAssociation] t1) a
GROUP BY GroupId, Groups

ROLLBACK

GroupId     Groups
----------- -----------------
269         B1
270         B2
271         B,B1,B2,A
272         A,B1,B
273         C
274         D

GroupIds    Groups
----------- -----------------
269,271     B1,B2,B
270           B2
271           B,B1,B2,A
272           B,B1,B2,A (because B is part of A and A is part of B so all groups under B are part of A)
273           C
274           D
273,271     B1,B2,B,A,C
274,272     B1,B2,B,A,D

;WITH rCTE AS (
	SELECT Level = 1, Id, GroupId, GroupName, ParentGroupId, ParentGroupName   
	FROM dbo.groupsAssociation 
	WHERE [GroupName] IN ('A') --('A','C','D')
	UNION ALL 
	SELECT Level = Level + 1, tr.Id, tr.GroupId, tr.GroupName, tr.ParentGroupId, tr.ParentGroupName
	FROM dbo.groupsAssociation tr 
	INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
	WHERE lr.Level < 8
) 
SELECT GroupId, GroupName, ParentGroupId, ParentGroupName 
FROM rCTE
GROUP BY GroupId, GroupName
ORDER BY GroupName

Alter FUNCTION [dbo].[GetAllSubGroupsByList]           
(          
 -- Add the parameters for the function here          
 @GroupIdsList varchar(500)       
)    
RETURNS @Table Table (GroupId int)  
AS       
BEGIN  
Declare @str varchar(500)
;WITH rCTE AS (
        SELECT Level = 1, Id, GroupId, GroupName --, ParentGroupId, ParentGroupName   
        FROM dbo.groupsAssociation 
        -- WHERE [GroupName] IN ('A') --('A','C','D')
        WHERE [GroupId] IN (Select ColumnData from [dbo].[fn_CSVToTable](@GroupIdsList))
        UNION ALL 
        SELECT Level = Level + 1, tr.Id, tr.GroupId, tr.GroupName --, tr.ParentGroupId, tr.ParentGroupName
        FROM dbo.groupsAssociation tr 
        INNER JOIN rCTE lr ON lr.GroupId = tr.ParentGroupId
        WHERE lr.Level < 8
) 
insert into @Table(GroupId)  
SELECT GroupId FROM rCTE GROUP BY GroupId
return
END

 SELECT  
        IsNull(U.Id,0) as CustomUserUserId,      
        UP.ScreenName as CustomUserScreenName,      
        UP.EmailAddress as CustomUserEmailAddress,      
        UP.FirstName as CustomUserFirstName,      
        UP.LastName as CustomUserLastName,      
        U.TenantId as CustomUserTenantId,  
        T.TenantName as CustomUserTenantName,     
        IsNull(UG.GroupId ,0) as CustomUserGroupId,   
        IsNull(G.Name,'''') as CustomUserGroupName      
      FROM  
      UserProfile UP  
      inner join Users U on U.UserProfileId = UP.Id  
      inner join Tenant T on U.TenantId = T.Id  
      inner join UserGroups UG on t.Id = UG.TenantId and UG.UserId = U.Id  
      inner join [Group] G on UG.GroupId = G.Id   
      where U.IsActive = 1 and U.IsDeleted = 0 and UP.IsActive = 1 and UP.IsDeleted = 0 and UG.IsActive = 1 and U.UserTypeId != 4  and   
   UG.GroupId in (select GroupId from dbo.[GetAllSubGroupsByList](@listOfIds))  
       Order By U.Id