UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID

CREATE function dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]
(
  @MARKETINGPLANITEMID uniqueidentifier = null
)
returns @HIERARCHY table (
  [ID] uniqueidentifier,
  [PARENTMARKETINGPLANITEMID] uniqueidentifier,
  [NAME] nvarchar(100),
  [LEVEL] integer,
  [ISAPPROVED] bit
)
as
begin
  declare @LEVEL AS int;
  select @LEVEL = [LEVEL] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID;
  set @LEVEL = isnull(@LEVEL, 0);

  insert into @HIERARCHY 
  select
    [ID],
    [PARENTMARKETINGPLANITEMID],
    [NAME],
    [LEVEL],
    [ISAPPROVED]
  from dbo.[MKTMARKETINGPLANITEM]
  where [ID] = @MARKETINGPLANITEMID
  or (@MARKETINGPLANITEMID is null and [PARENTMARKETINGPLANITEMID] is null);

  while (@@ROWCOUNT > 0)
    begin 
      set @LEVEL = @LEVEL + 1;

      insert into @HIERARCHY
      select
        [MPI].[ID],
        [MPI].[PARENTMARKETINGPLANITEMID],
        [MPI].[NAME],
        [MPI].[LEVEL],
        [MPI].[ISAPPROVED]
      from dbo.[MKTMARKETINGPLANITEM] as [MPI]
      inner join @HIERARCHY as [HIER] on [MPI].[PARENTMARKETINGPLANITEMID] = [HIER].[ID] and [MPI].[LEVEL] = @LEVEL
      order by [MPI].[NAME];
    end

  return;
end