USP_PLANMAILINGSEARCH

CREATE procedure dbo.[USP_PLANMAILINGSEARCH]
(
  @MAXROWS smallint,
  @MAILINGNAME nvarchar(100) = null,
  @PLANID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEID uniqueidentifier = null
)
as
  set nocount on;

  declare @TEMPTABLE table([ID] uniqueidentifier, [MAILING] nvarchar(100), [PLAN] nvarchar(256), [SITE] nvarchar(1024));

  declare MAILINGCURSOR cursor local fast_forward for
  select
    M.[ID],
    M.[NAME],
    M.[LEVEL],
    dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME([P].[SITEID]) as [SITE]
  from
    dbo.[MKTMARKETINGPLANITEM] M
  inner join 
    dbo.[MKTMARKETINGPLAN] P on P.[ID] = M.[MARKETINGPLANID]
  where
     dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, P.[SITEID]) = 1
  and
    M.[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] T where M.[MARKETINGPLANID] = T.[MARKETINGPLANID])
  and
    (@PLANID is null or M.[MARKETINGPLANID] = @PLANID)
  and
    (@SITEID is null or P.[SITEID] = @SITEID)
  and
    (M.[NAME] like coalesce(@MAILINGNAME, '') + '%')

  declare @ID uniqueidentifier;
  declare @NAME nvarchar(100);
  declare @LEVEL int;
  declare @SITE nvarchar(1024);

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

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

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

  close MAILINGCURSOR;
  deallocate MAILINGCURSOR;

  select
    *
  from
    @TEMPTABLE
  order by
    [MAILING];

  return 0;