UFN_NFGLEVEL_GETFULLPATHNAME

CREATE function dbo.UFN_NFGLEVEL_GETFULLPATHNAME
(
  @ID uniqueidentifier,
  @SEPARATOR nvarchar(10) = null,
  @CONTAINNFGNAME bit = 0
)
returns nvarchar(500)
with execute as caller
as begin

  declare @NFGCAMPAIGNID uniqueidentifier,
          @CURRENTPATH hierarchyid,
          @ROOTPATH hierarchyid,
          @FULLNAME nvarchar(500) = '',
          @TEMPNAME nvarchar(50);

  if @SEPARATOR is null
    set @SEPARATOR = '/'

  select @CURRENTPATH = HIERARCHYPATH, @NFGCAMPAIGNID = NFGCAMPAIGNID from FAFNFGCAMPAIGNLEVEL where ID = @ID

  select @ROOTPATH = MIN(HIERARCHYPATH) from FAFNFGCAMPAIGNLEVEL where NFGCAMPAIGNID = @NFGCAMPAIGNID

  while @CURRENTPATH is not null and @CURRENTPATH.GetLevel() > 0 and (@CURRENTPATH <> @ROOTPATH or @CONTAINNFGNAME = 1)
  begin
    select @TEMPNAME = NAME from FAFNFGCAMPAIGNLEVEL where HIERARCHYPATH = @CURRENTPATH
    if @FULLNAME = ''
      set @FULLNAME = @TEMPNAME
    else
      set @FULLNAME = @TEMPNAME + @SEPARATOR + @FULLNAME
    set @CURRENTPATH = @CURRENTPATH.GetAncestor(1)
  end

    return @FULLNAME
end