UFN_MKTMARKETINGPLANITEMNAVIGATIONHIERARCHY

CREATE function dbo.[UFN_MKTMARKETINGPLANITEMNAVIGATIONHIERARCHY]
(
  @MARKETINGPLANITEMID uniqueidentifier=null
)
/*
Returns a table of template items that have been assigned to the given MARKETINGPLANID
*/
returns @HIERARCHY table (
  [ID] uniqueidentifier null, --not null primary key,

  [PARENTMARKETINGPLANITEMID] uniqueidentifier NULL,
  [NAME] nvarchar(100),
  [CAPTION] nvarchar(50),
  [BACKCOLOR] int
)
as
begin
  declare @MARKETINGPLANID uniqueidentifier;
  select top 1
    @MARKETINGPLANID = [MARKETINGPLANID]
  from
    dbo.[MKTMARKETINGPLANITEM]
  where
    ID=@MARKETINGPLANITEMID;

  declare @TOPMARKETINGPLANITEMID uniqueidentifier;
  select top 1
    @TOPMARKETINGPLANITEMID = [ID]
  from
    dbo.[MKTMARKETINGPLANITEM]
  where
    [MARKETINGPLANID]=@MARKETINGPLANID
  and
    [LEVEL]=0;

  declare @lvl AS int;
  select
    @lvl = [LEVEL]
  from
    dbo.[MKTMARKETINGPLANITEM]
  where
    [ID]=@TOPMARKETINGPLANITEMID;

  set @lvl=coalesce(@lvl,0);

  /* populate @HIERARCHY with the parent item */
  insert into @HIERARCHY
  select
    M.[ID],
    M.[PARENTMARKETINGPLANITEMID],
    M.[NAME],
    T.[CAPTION],
    T.[BACKCOLOR]
  from
    dbo.MKTMARKETINGPLANITEM M
  inner join
    dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] T on (M.MARKETINGPLANID=T.MARKETINGPLANID and M.LEVEL=T.LEVEL)
  where
    (M.[ID] = @TOPMARKETINGPLANITEMID) or (@TOPMARKETINGPLANITEMID is null and M.[LEVEL]=0);

  /* populate @items with the child items recursively */
  while @@rowcount > 0
  begin
    set @lvl = @lvl + 1
    insert into @HIERARCHY
    select 
      MPI.[ID],
      MPI.[PARENTMARKETINGPLANITEMID],
      MPI.[NAME],
      T.[CAPTION],
      T.[BACKCOLOR]
    from
      dbo.MKTMARKETINGPLANITEM MPI
    inner join
      dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] T on (MPI.MARKETINGPLANID=T.MARKETINGPLANID and MPI.LEVEL=T.LEVEL)
    inner join
      @HIERARCHY tMPI on MPI.PARENTMARKETINGPLANITEMID = tMPI.ID and MPI.[LEVEL] = @lvl
    order by MPI.[NAME];
  end
  return;
end