UFN_CLIENTROLE_GETPARENTIDS

create function dbo.UFN_CLIENTROLE_GETPARENTIDS(@CHILDROLEID int)
returns 
@PARENTROLES table 
(
    ID int
)
with execute as caller
as
begin
    with PARENTROLES(ROLEID)
    as
    (
        --Anchor member definition

        select CRC.[PARENTCLIENTROLEID]
        from [DBO].CLIENTROLECHILD as CRC
        where CRC.[CHILDCLIENTROLEID] = @CHILDROLEID
        union all
        --Recursive member definition

        select    CRC.[PARENTCLIENTROLEID]
        from [DBO].CLIENTROLECHILD as CRC
        inner join PARENTROLES as PARENTS
        on CRC.CHILDCLIENTROLEID = PARENTS.ROLEID
    )

    insert into @PARENTROLES
    select * from PARENTROLES OPTION (MAXRECURSION 100)

    insert into @PARENTROLES (ID) values(@CHILDROLEID)

    return 
end