UFN_CLIENTSITE_PARENTIDS

create function dbo.UFN_CLIENTSITE_PARENTIDS(@CHILDSITEID int)
returns 
@PARENTSITES table 
(
    ID int
)
with execute as caller
as
begin
    with PARENTSITES(SITEID)
    as
    (
        --Anchor member definition

        select CS.PARENTSITEID
        from [DBO].CLIENTSITES as CS
        where CS.ID = @CHILDSITEID
        union all
        --Recursive member definition

        select    CS.PARENTSITEID
        from [DBO].CLIENTSITES as CS
        inner join PARENTSITES as CHILDREN
        on CS.ID = CHILDREN.SITEID
        where CS.PARENTSITEID is not null
    )

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

    return 
end