Thank you very much for your help.

my final function now looks as follows:

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

and I am using it here NO need to return as string:

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

Shamshad Ali.