USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS

CREATE procedure dbo.USP_MKTSEGMENTATIONTESTSEGMENT_RESEQUENCETESTSEGMENTS
(
  @TESTSEGMENTCURSOR cursor varying output,        /* Required. The unopened cursor that defines which test segments to resequence. */
  @SEQUENCE int output,                            /* Required. The sequence to start re-numbering the test segments from. */
  @CHANGEAGENTID uniqueidentifier = null,          /* Optional. The user ID that is making the change. */
  @CURRENTDATE datetime = null                     /* Optional. The date the change is being made. */
)
as
  set nocount on;

  declare @TESTSEGMENTID uniqueidentifier;

  begin try
    open @TESTSEGMENTCURSOR;
    fetch next from @TESTSEGMENTCURSOR into @TESTSEGMENTID;

    if @@FETCH_STATUS = 0
      begin
        declare @PARENTSEGMENTID uniqueidentifier;

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

        if @CURRENTDATE is null
          set @CURRENTDATE = getdate();

        select
          @PARENTSEGMENTID = [SEGMENTID]
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        where [ID] = @TESTSEGMENTID;

        /* Update the test segments */
        while (@@FETCH_STATUS = 0)
          begin
            update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
              [SEQUENCE] = @SEQUENCE,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @TESTSEGMENTID;

            -- clear the cached information for the parent segment to force mailing data recalculation

            exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @PARENTSEGMENTID, 0, 1;

            set @SEQUENCE = @SEQUENCE + 1;

            fetch next from @TESTSEGMENTCURSOR into @TESTSEGMENTID;
          end;
      end

    close @TESTSEGMENTCURSOR;
    deallocate @TESTSEGMENTCURSOR;
  end try

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

  return 0;