USP_MKTMARKETINGPLANITEM_DELETE

CREATE procedure dbo.[USP_MKTMARKETINGPLANITEM_DELETE]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @startlvl AS int;
  declare @lvl AS int; 
  declare @contextCache varbinary(128);

  set @contextCache = CONTEXT_INFO();
  set CONTEXT_INFO @CHANGEAGENTID;

  begin try
    select
      @lvl = [LEVEL]
    from
      dbo.[MKTMARKETINGPLANITEM]
    where
      [ID] = @ID;
    select @startlvl=@lvl;

    declare @items table (
      [ID] uniqueidentifier not null primary key,
      [PARENTMARKETINGPLANITEMID] uniqueidentifier NULL,
      [LEVEL] int not null
    );

    /* populate @items with the parent item */
    insert into @items
    select
      [ID],
      [PARENTMARKETINGPLANITEMID],
      [LEVEL]
    from
      dbo.[MKTMARKETINGPLANITEM]
    where
      [ID]=@ID;

    /* populate @items with the child items recursively */
    while @@rowcount > 0 
    begin 
      set @lvl = @lvl + 1
      insert into @items
      select
        [MPI].[ID],
        [MPI].[PARENTMARKETINGPLANITEMID],
        [MPI].[LEVEL]
      from
        dbo.[MKTMARKETINGPLANITEM] [MPI]
      inner join
        @items tMPI on [MPI].[PARENTMARKETINGPLANITEMID] = tMPI.[ID] and tMPI.[LEVEL] = @lvl - 1; 
    end

    -- Should not be able to delete plan if there is an effort created using the plan.

    if exists (select 1 from dbo.[MKTSEGMENTATION] where [MARKETINGPLANITEMID] in (select [ID] from @items))
      raiserror('BBERR_MARKETINGEFFORTEXISTS', 13, 1);

    /* delete the items */
    while @lvl >= @startlvl
    begin
      delete from MPI
      from
        dbo.[MKTMARKETINGPLANITEM] [MPI]
      inner join
        @items tMPI on ([MPI].[ID] = tMPI.[ID] and [MPI].[LEVEL]=@lvl);

      set @lvl = @lvl - 1
    end;

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
      set CONTEXT_INFO @contextCache;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;