UFN_MKTMARKETINGPLANITEM_PATH

CREATE function dbo.[UFN_MKTMARKETINGPLANITEM_PATH]
(
  @MARKETINGPLANITEMID uniqueidentifier,
  @RETURNFULLPATH bit
)
returns nvarchar(max)
as
begin
  declare @PATH nvarchar(max);
  declare @NAME nvarchar(100);
  declare @LEVEL integer;

  select
    @LEVEL = [LEVEL]
  from dbo.[MKTMARKETINGPLANITEM]
  where [ID] = @MARKETINGPLANITEMID;

  if @RETURNFULLPATH = 1 set @LEVEL = @LEVEL + 1

  declare PATHCURSOR cursor local fast_forward for
  select [NAME]
  from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY](@MARKETINGPLANITEMID)
  where [LEVEL] < @LEVEL
  order by [LEVEL];

  open PATHCURSOR;
  fetch next from PATHCURSOR into @NAME;

  set @PATH = '';

  while (@@FETCH_STATUS = 0)
    begin
      if len(@PATH) > 0 set @PATH = @PATH + ' \ ';
      set @PATH = @PATH + @NAME;
      fetch next from PATHCURSOR into @NAME;
    end

  close PATHCURSOR;
  deallocate PATHCURSOR;

  return @PATH;
end