UFN_CAMPAIGN_GETPATH

            create function dbo.UFN_CAMPAIGN_GETPATH(
                @CAMPAIGNID uniqueidentifier
            )
            returns nvarchar(1000)
            as
            begin
                declare @PATH nvarchar(1000);

                with CTE_BASEUP(ID, PARENTCAMPAIGNID, NAME)
                as(
                    select
                        CAMPAIGN.ID,
                        (    
                            select 
                                PARENTCAMPAIGN.ID 
                            from 
                                dbo.CAMPAIGN [PARENTCAMPAIGN] 
                            where 
                                CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = PARENTCAMPAIGN.HIERARCHYPATH
                        ) as PARENTCAMPAIGNID,
                        CAMPAIGN.NAME
                    from dbo.CAMPAIGN
                    where CAMPAIGN.ID = @CAMPAIGNID

                    union all

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

                    union all

                    select
                        CTE_BASEUP.ID,
                        CTE_BASEUP.PARENTCAMPAIGNID,
                        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.PARENTCAMPAIGNID

                )
                select @PATH=PATH
                from CTE_ROOTDOWN
                where ID = @CAMPAIGNID;

                return @PATH;
            end