UFN_CAMPAIGN_GETROOTID

            create function dbo.UFN_CAMPAIGN_GETROOTID
            (
                @CAMPAIGNID uniqueidentifier
            )
            returns uniqueidentifier
            as
            begin
                declare @HIERARCHYPATH hierarchyid;
                select @HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH from dbo.CAMPAIGN where CAMPAIGN.ID = @CAMPAIGNID;

                declare @ROOTCAMPAIGNID uniqueidentifier;
                select
                    @ROOTCAMPAIGNID = CAMPAIGN.ID
                from
                    dbo.CAMPAIGN
                where
                    @HIERARCHYPATH.IsDescendantOf(CAMPAIGN.HIERARCHYPATH) = 1
                and
                    CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = hierarchyid::GetRoot();

                return @ROOTCAMPAIGNID
            end