USP_SIMPLEDATALIST_MKTPLANMAILING

CREATE procedure dbo.USP_SIMPLEDATALIST_MKTPLANMAILING
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  declare @TEMPTABLE table([VALUE] uniqueidentifier, [LABEL] nvarchar(256));

  declare MAILINGCURSOR cursor local fast_forward for
  select
    [MKTMARKETINGPLANITEM].[ID],
    [MKTMARKETINGPLANITEM].[NAME],
    [MKTMARKETINGPLANITEM].[LEVEL]
  from dbo.[MKTMARKETINGPLANITEM]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
  where [MKTMARKETINGPLANITEM].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] T where [MKTMARKETINGPLANITEM].[MARKETINGPLANID] = T.[MARKETINGPLANID])
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTMARKETINGPLAN].[SITEID] or (SITEID is null and [MKTMARKETINGPLAN].[SITEID] is null)));

  declare @ID uniqueidentifier;
  declare @NAME nvarchar(256);
  declare @LEVEL int;

  open MAILINGCURSOR;
  fetch next from MAILINGCURSOR into @ID, @NAME, @LEVEL;

  while (@@FETCH_STATUS = 0)
  begin
    if (@LEVEL = 1)
      select @NAME = [LEVEL0].[NAME] + ' / ' + @NAME
      from dbo.[MKTMARKETINGPLANITEM] as [LEVEL1]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL0] on [LEVEL0].[ID] = [LEVEL1].[PARENTMARKETINGPLANITEMID]
      where [LEVEL1].[ID] = @ID;
    if (@LEVEL = 2)
      select @NAME = [LEVEL0].[NAME] + ' / ' + [LEVEL1].[NAME] + ' / ' + @NAME
      from dbo.[MKTMARKETINGPLANITEM] as [LEVEL2]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL1] on [LEVEL1].[ID] = [LEVEL2].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL0] on [LEVEL0].[ID] = [LEVEL1].[PARENTMARKETINGPLANITEMID]
      where [LEVEL2].[ID] = @ID;
    if (@LEVEL = 3)
      select @NAME = [LEVEL0].[NAME] + ' / ' + [LEVEL1].[NAME] + ' / ' + [LEVEL2].[NAME] + ' / ' + @NAME
      from dbo.[MKTMARKETINGPLANITEM] as [LEVEL3]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL2] on [LEVEL2].[ID] = [LEVEL3].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL1] on [LEVEL1].[ID] = [LEVEL2].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL0] on [LEVEL0].[ID] = [LEVEL1].[PARENTMARKETINGPLANITEMID]
      where [LEVEL3].[ID] = @ID;
    if (@LEVEL = 4)
      select @NAME = [LEVEL0].[NAME] + ' / ' + [LEVEL1].[NAME] + ' / ' + [LEVEL2].[NAME] + ' / ' + [LEVEL3].[NAME] + ' / ' + @NAME
      from dbo.[MKTMARKETINGPLANITEM] as [LEVEL4]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL3] on [LEVEL3].[ID] = [LEVEL4].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL2] on [LEVEL2].[ID] = [LEVEL3].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL1] on [LEVEL1].[ID] = [LEVEL2].[PARENTMARKETINGPLANITEMID]
      inner join dbo.[MKTMARKETINGPLANITEM] as [LEVEL0] on [LEVEL0].[ID] = [LEVEL1].[PARENTMARKETINGPLANITEMID]    
      where [LEVEL4].[ID] = @ID;

    insert into @TEMPTABLE values (@ID, @NAME);
    fetch next from MAILINGCURSOR into @ID, @NAME, @LEVEL;
  end

  close MAILINGCURSOR;
  deallocate MAILINGCURSOR;

  select
    [VALUE],
    [LABEL]
  from @TEMPTABLE
  order by [LABEL];

  return 0;