USP_MKTSEGMENTLISTDEDUPEPROCESS_UPDATERECORDCOUNTS

CREATE procedure dbo.[USP_MKTSEGMENTLISTDEDUPEPROCESS_UPDATERECORDCOUNTS]
(
  @SEGMENTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @TOTALRECORDCOUNT int = null output,
  @DUPLICATERECORDCOUNT int = null output
)
as
  set nocount on;

  declare @SEGMENTLISTID uniqueidentifier;
  declare @SQL nvarchar(500);
  declare @DUPLICATERECORDCOUNTDELTA int;

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

    select
      @SEGMENTLISTID = [CURRENTSEGMENTLISTID]
    from dbo.[MKTSEGMENT]
    where [ID] = @SEGMENTID;

    --Get the duplicate count...

    set @SQL = 'select @DUPLICATERECORDCOUNT = count(*) from dbo.[' + dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORIDSET](@SEGMENTLISTID, 1) + ']';
    exec sp_executesql @SQL, N'@DUPLICATERECORDCOUNT int output', @DUPLICATERECORDCOUNT = @DUPLICATERECORDCOUNT output;

    select 
      @DUPLICATERECORDCOUNTDELTA = (@DUPLICATERECORDCOUNT - isnull([DUPLICATERECORDCOUNT], 0))
    from dbo.[MKTSEGMENTLIST]
    where [ID] = @SEGMENTLISTID;

    --Update the quantities so that the rental and exchange numbers stay in-sync with the total record count.  If duplicates are found,

    --then subtract them from the rental quantity first.  If we use up the rental quantity, then subtract from the exchange quantity.

    update dbo.[MKTSEGMENTLIST] set
      [RENTALQUANTITY] = (case when [RENTALQUANTITY] > @DUPLICATERECORDCOUNTDELTA then ([RENTALQUANTITY] - @DUPLICATERECORDCOUNTDELTA) else 0 end),
      [EXCHANGEQUANTITY] = [EXCHANGEQUANTITY] - (case when @DUPLICATERECORDCOUNTDELTA > [RENTALQUANTITY] then (@DUPLICATERECORDCOUNTDELTA - [RENTALQUANTITY]) else 0 end),
      [DUPLICATERECORDCOUNT] = @DUPLICATERECORDCOUNT,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [ID] = @SEGMENTLISTID;

    --Return the total record count...

    select
      @TOTALRECORDCOUNT = [TOTALRECORDCOUNT]
    from dbo.[MKTSEGMENTLIST]
    where [ID] = @SEGMENTLISTID;
  end try

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

  return 0;