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.