UFN_MKTMARKETINGPLANITEMHIERARCHY

Returns a table with they keys and captions for all parent items in a marketing plan item's hierarchy

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@MARKETINGPLANITEMID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]
(
  @MARKETINGPLANITEMID uniqueidentifier
)
returns @HIERARCHY table (
  [ID] uniqueidentifier null,
  [PARENTMARKETINGPLANITEMID] uniqueidentifier,
  [CAPTION] nvarchar(100),
  [NAME] nvarchar(100),
  [LEVEL] int
)
as
begin
  declare @LEVEL as integer;
  select @LEVEL = [LEVEL] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID;

  insert into @HIERARCHY 
  select 
    [ID],
    [PARENTMARKETINGPLANITEMID],
    '',
    [NAME],
    [LEVEL] 
  from dbo.[MKTMARKETINGPLANITEM]
  where [ID] = @MARKETINGPLANITEMID;

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

      insert into @HIERARCHY
      select
        [MPI].[ID],
        [MPI].[PARENTMARKETINGPLANITEMID],
        (select [MPITI].[CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL]),
        [MPI].[NAME],
        [MPI].[LEVEL]
      from dbo.[MKTMARKETINGPLANITEM] as [MPI]
      inner join @HIERARCHY as [HIER] on [HIER].[PARENTMARKETINGPLANITEMID] = [MPI].[ID] and [MPI].[LEVEL] = @LEVEL;
    end

  return;
end