UFN_GROUP_GETGROUPANDMEMBERS_ONELEVELDEEP

Returns a group and its members one level deep

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@GROUPID	uniqueidentifier	IN
@INCLUDEPREVIOUS	bit	IN

Definition

Copy



/* This function is purposefully not recursive it only returns group members and */

CREATE FUNCTION dbo.UFN_GROUP_GETGROUPANDMEMBERS_ONELEVELDEEP(@GROUPID uniqueidentifier, @INCLUDEPREVIOUS bit) returns TABLE
as
return 
(    with GROUPMEMBERS_CTE as
    (select 
        GROUPMEMBER.ID, 
        GROUPMEMBER.MEMBERID 
    from GROUPMEMBER 
    where GROUPID = @GROUPID
        and (@INCLUDEPREVIOUS = 1 or dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1)
    )
    select * 
    from GROUPMEMBERS_CTE
    union
    select 
        GROUPMEMBER.ID, 
        GROUPMEMBER.MEMBERID 
    from GROUPMEMBERS_CTE
    inner join GROUPMEMBER 
        on GROUPMEMBERS_CTE.MEMBERID = GROUPMEMBER.GROUPID
    where (@INCLUDEPREVIOUS = 1 or dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1)
    union 
    select null, @GROUPID
)