UFN_SITE_GETPATH_BULK

Given a site, return its path within its hierarchy. Optimized for use with cross apply.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@SITEID	uniqueidentifier	IN
Definition

Copy


        create function dbo.UFN_SITE_GETPATH_BULK(@SITEID uniqueidentifier)
        returns table
        as
        return
        (
            with CTE_BASEUP(ID, PARENTSITEID, NAME)
            as(
                select
                    SITE.ID,
                    (    
                        select 
                            PARENTSITE.ID 
                        from 
                            dbo.SITE [PARENTSITE] 
                        where 
                            SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                    ) as PARENTSITEID,
                    SITE.NAME
                from dbo.SITE
                where SITE.ID = @SITEID

                union all

                select
                    SITE.ID,
                    (    
                        select 
                            PARENTSITE.ID 
                        from 
                            dbo.SITE [PARENTSITE] 
                        where 
                            SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                    ) as SITEID,
                    SITE.NAME
                from dbo.SITE
                    inner join CTE_BASEUP on CTE_BASEUP.PARENTSITEID=SITE.ID
            ),
            CTE_ROOTDOWN(ID, PARENTSITEID, NAME, PATH)
            as(
                select
                    CTE_BASEUP.ID,
                    CTE_BASEUP.PARENTSITEID,
                    CTE_BASEUP.NAME,
                    convert(nvarchar(1000), CTE_BASEUP.NAME)
                from CTE_BASEUP
                where PARENTSITEID is null

                union all

                select
                    CTE_BASEUP.ID,
                    CTE_BASEUP.PARENTSITEID,
                    CTE_BASEUP.NAME,
                    convert(nvarchar(1000), CTE_ROOTDOWN.PATH + '\' + CTE_BASEUP.NAME)
                from CTE_BASEUP
                    inner join CTE_ROOTDOWN on CTE_ROOTDOWN.ID=CTE_BASEUP.PARENTSITEID

            )
            select ID,PATH as SITEPATH
            from CTE_ROOTDOWN
            where ID = @SITEID
        )