USP_MKTMARKETINGPLANBRIEF_RESEQUENCEBRIEFS

CREATE procedure dbo.[USP_MKTMARKETINGPLANBRIEF_RESEQUENCEBRIEFS]
(
  @BRIEFCURSOR cursor varying output,            /* Required. The unopened cursor that defines which briefs to resequence. */
  @SEQUENCE int output,                          /* Required. The sequence to start re-numbering the briefs 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 @BRIEFID uniqueidentifier;

  begin try
    open @BRIEFCURSOR;
    fetch next from @BRIEFCURSOR into @BRIEFID;

    if @@FETCH_STATUS = 0
    begin
      if @CHANGEAGENTID is null
        exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

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

      /* Update the briefs */
      while (@@FETCH_STATUS = 0)
      begin
        update
          dbo.[MKTMARKETINGPLANBRIEF]
        set
          [SEQUENCE] = @SEQUENCE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where
          [ID] = @BRIEFID;

        set @SEQUENCE = @SEQUENCE + 1;
        fetch next from @BRIEFCURSOR into @BRIEFID;
      end;
    end

    close @BRIEFCURSOR;
    deallocate @BRIEFCURSOR;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;