USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_DRAGDROPINSERT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_DRAGDROPINSERT
(
  @ID uniqueidentifier,                        /* Required. The segment ID the test segments are being inserted into. */
  @CHANGEAGENTID uniqueidentifier = null,      /* Optional. The user ID that is making the change. */
  @TESTSEGMENTS xml,                           /* Required. The test segment IDs to insert at the specified sequence. */
  @SEQUENCE int                                /* Required. The sequence to insert the test segments at. */
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @TESTSEGMENTSTABLE table([ID] uniqueidentifier);
  declare @ORIGINALSEGMENTID uniqueidentifier;
  declare @TOPSEQ int;
  declare @BOTTOMSEQ int;
  declare @SEQ int;
  declare @CURSOR cursor;

  begin try
    select
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    where [ID] = @ID;

    --Check if the mailing is currently being activated...

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @CURRENTDATE = GetDate();

    --Put the test segment IDs into a temp table

    insert into @TESTSEGMENTSTABLE
      select T.c.value('(ID)[1]','uniqueidentifier') as 'ID'
      from @TESTSEGMENTS.nodes('/TESTSEGMENTS/ITEM') T(c);

    --Get the top and bottom sequences for the test segments being inserted

    select 
      @ORIGINALSEGMENTID = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID],
      @TOPSEQ = min([MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE]), 
      @BOTTOMSEQ = max([MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE])
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join @TESTSEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
    group by [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID];

    if @@ROWCOUNT > 1
      begin
        raiserror ('Test segments must originate from the same segment.',13,1);
        return 1;
      end

    if @ID = @ORIGINALSEGMENTID
      begin
        /**************************************************/
        /* Dropping test segments within the same segment */
        /**************************************************/

        if @SEQUENCE < @TOPSEQ
          set @SEQ = @SEQUENCE;
        else
          set @SEQ = @TOPSEQ;

        if @TOPSEQ <= (@SEQUENCE - 1)
          begin
            --Update the other test segments sequences before the drop node first

            set @CURSOR = cursor local fast_forward for
              select [ID]
              from dbo.[MKTSEGMENTATIONTESTSEGMENT]
              where [SEGMENTID] = @ID
              and [SEQUENCE] between @TOPSEQ and (@SEQUENCE - 1)
              and [ID] not in (select [ID] from @TESTSEGMENTSTABLE)
              order by [SEQUENCE];
            exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;
          end
      end
    else
      begin
        /***********************************************/
        /* Dropping test segments into another segment */
        /***********************************************/

        --Change the segment ID for the test segments being inserted

        update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
          [SEGMENTID] = @ID, [CHANGEDBYID]=@CHANGEAGENTID, [DATECHANGED]=@CURRENTDATE
        where [SEGMENTID] = @ORIGINALSEGMENTID
        and [ID] in (select [ID] from @TESTSEGMENTSTABLE);

        --Update all test segments that came after the test segments being moved to another segment

        set @CURSOR = cursor local fast_forward for
          select [ID]
          from dbo.[MKTSEGMENTATIONTESTSEGMENT]
          where [SEGMENTID] = @ORIGINALSEGMENTID
          and [SEQUENCE] >= @TOPSEQ
          order by [SEQUENCE];
        exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS] @CURSOR, @TOPSEQ, @CHANGEAGENTID, @CURRENTDATE;

        set @SEQ = @SEQUENCE;
      end

    --Update the inserted test segments sequences

    set @CURSOR = cursor local fast_forward for
      select [ID] from @TESTSEGMENTSTABLE;
    exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;

    if @SEQUENCE <= @BOTTOMSEQ
      begin
        --Now update the other test segments sequences after the drop node

        set @CURSOR = cursor local fast_forward for
          select [ID]
          from dbo.[MKTSEGMENTATIONTESTSEGMENT]
          where [SEGMENTID] = @ID
          and [SEQUENCE] between @SEQUENCE and @BOTTOMSEQ
          and [ID] not in (select [ID] from @TESTSEGMENTSTABLE)
          order by [SEQUENCE];
        exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;
      end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;