UFN_CLIENTROLE_GETCHILDIDS

create function dbo.UFN_CLIENTROLE_GETCHILDIDS(@PARENTROLEID int)
returns 
@CHILDROLES table 
(
    ID int
)
with execute as caller
as
begin

    declare @CHILDROLESTEMP table 
    (
        ID int
    );

    with CHILDROLES(ROLEID)
    as
    (
        --Anchor member definition

        select CRC.[CHILDCLIENTROLEID]
        from [DBO].CLIENTROLECHILD as CRC
        inner join dbo.ClientRoles CR on CR.ID = CRC.CHILDCLIENTROLEID
        where CR.deleted = 0 and CRC.[PARENTCLIENTROLEID] = @PARENTROLEID
        union all
        --Recursive member definition

        select    CRC.[CHILDCLIENTROLEID]
        from [DBO].CLIENTROLECHILD as CRC
        inner join CHILDROLES as CHILDREN
        on CRC.PARENTCLIENTROLEID = CHILDREN.ROLEID
        inner join dbo.ClientRoles CR on CR.ID = CRC.CHILDCLIENTROLEID
        where CR.deleted = 0
    )



    insert into @CHILDROLESTEMP
    select * from CHILDROLES OPTION (MAXRECURSION 100)

    insert into @CHILDROLES
    select distinct ID from @CHILDROLESTEMP

    return 
end