UFN_CLIENTSITE_GETCHILDIDS

create function dbo.UFN_CLIENTSITE_GETCHILDIDS(@PARENTSITEID int) returns table
as return
    --TWG 05/15/2013 inlining this function

    --"OPTION (MAXRECURSION 100)" can be used when this function is called, but the default value is 100 anyway

    with CHILDSITES(SITEID) as
    (
        --Anchor member definition

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

        select    CS.ID
        from [DBO].CLIENTSITES as CS
        inner join CHILDSITES as CHILDREN
        on CS.PARENTSITEID = CHILDREN.SITEID
    )
    select SITEID as ID from CHILDSITES;