USP_MKTSEGMENTATIONSEGMENT_RESEQUENCESEGMENTS

CREATE procedure dbo.USP_MKTSEGMENTATIONSEGMENT_RESEQUENCESEGMENTS
(
  @SEGMENTCURSOR cursor varying output,            /* Required. The unopened cursor that defines which segments to resequence. */
  @SEQUENCE int output,                            /* Required. The sequence to start re-numbering the segments from. */
  @MARKETINGPLANBRIEFID uniqueidentifier = null,   /* Optional. The brief to insert the segments into. */
  @NEXTBRIEFSEQUENCE int = -1,                     /* Optional. The brief sequence that comes after the segments being inserted. */
  @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 @SEGMENTID uniqueidentifier;
  declare @BRIEFID uniqueidentifier;

  begin try
    open @SEGMENTCURSOR;
    fetch next from @SEGMENTCURSOR into @SEGMENTID, @BRIEFID;

    if @@FETCH_STATUS = 0
      begin
        declare @SQL nvarchar(max);
        declare @SEGMENTSQL nvarchar(max);
        declare @SEGMENTPARAMDEF nvarchar(max);
        declare @BRIEFSQL nvarchar(max);
        declare @BRIEFPARAMDEF nvarchar(max);
        declare @LASTBRIEFID uniqueidentifier;

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

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

        /* Create the SQL statement for updating the segments */
        set @SEGMENTSQL = 'update dbo.[MKTSEGMENTATIONSEGMENT] set [SEQUENCE] = @SEQUENCE, ###REPLACE###[CHANGEDBYID] = @CHANGEAGENTID, [DATECHANGED] = @CURRENTDATE where [ID] = @SEGMENTID';
        set @BRIEFSQL = replace(@SEGMENTSQL, '###REPLACE###', '');
        if @MARKETINGPLANBRIEFID is not null
          begin
            if @MARKETINGPLANBRIEFID = '00000000-0000-0000-0000-000000000001'
              set @SQL = '[MARKETINGPLANBRIEFID] = null, ';
            else
              set @SQL = '[MARKETINGPLANBRIEFID] = @MARKETINGPLANBRIEFID, ';
          end
        set @SQL = isnull(@SQL,'') + '[NEXTBRIEFSEQUENCE] = (case when @NEXTBRIEFSEQUENCE = -1 then [NEXTBRIEFSEQUENCE] else @NEXTBRIEFSEQUENCE end), ';
        if @NEXTBRIEFSEQUENCE is null
          set @NEXTBRIEFSEQUENCE = -1;
        set @SEGMENTSQL = replace(@SEGMENTSQL, '###REPLACE###', isnull(@SQL,''));

        /* Create the parameter definitions */
        set @BRIEFPARAMDEF = '@SEQUENCE int, @SEGMENTID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime';
        set @SEGMENTPARAMDEF = @BRIEFPARAMDEF + ', @MARKETINGPLANBRIEFID uniqueidentifier, @NEXTBRIEFSEQUENCE int';

        /* Update the segments */
        while (@@FETCH_STATUS = 0)
        begin
          if @BRIEFID is null
            exec sp_executesql @SEGMENTSQL, @SEGMENTPARAMDEF, @SEQUENCE = @SEQUENCE, @SEGMENTID = @SEGMENTID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE, @MARKETINGPLANBRIEFID = @MARKETINGPLANBRIEFID, @NEXTBRIEFSEQUENCE = @NEXTBRIEFSEQUENCE;
          else
            begin
              exec sp_executesql @BRIEFSQL, @BRIEFPARAMDEF, @SEQUENCE = @SEQUENCE, @SEGMENTID = @SEGMENTID, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;

              if @NEXTBRIEFSEQUENCE > -1
                begin
                  if (@LASTBRIEFID is null) or (@BRIEFID <> @LASTBRIEFID)
                    set @NEXTBRIEFSEQUENCE = @NEXTBRIEFSEQUENCE + 1;
                  set @LASTBRIEFID = @BRIEFID;
                end
            end

          set @SEQUENCE = @SEQUENCE + 1;
          fetch next from @SEGMENTCURSOR into @SEGMENTID, @BRIEFID;
        end;
      end

    close @SEGMENTCURSOR;
    deallocate @SEGMENTCURSOR;
  end try

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

  return 0;