USP_SPLITS_GETPRORATEDSPLITS

create procedure dbo.USP_SPLITS_GETPRORATEDSPLITS
(
    @ORIGINALAMOUNT money,
    @NEWAMOUNT money,
    @AMOUNTSTOPRORATE xml,
    @CREDITPAYMENTID uniqueidentifier = null  -- This parameter doesn't do anything except get selected again at the end, if it has a value (so that a caller can avoid an extra table variable in some cases).

)
as begin
    -- adjust splits for new amount

    declare @CURRENTAMOUNT decimal(30, 5);
    declare @WEIGHT decimal(30, 10);
    declare @ID uniqueidentifier;

    -- Load return table with current amounts to prorate

    -- Using "select into" due to a bug in SQL Server's optimizer. See the following article:

    -- http://connect.microsoft.com/SQLServer/feedback/details/727913/insert-from-xml-variable-really-slow-but-fast-with-select-into

    select
        T.c.value('(@AMOUNT)[1]','money') AS 'AMOUNT',
        T.c.value('(@ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(@CREDITITEMID)[1]','uniqueidentifier') AS 'CREDITITEMID'
    into #PRORATEDAMOUNTS
    from @AMOUNTSTOPRORATE.nodes('/AMOUNTSTOPRORATE/ITEM') T(c);

    -- Loop through and calculate new split amounts

    declare AMOUNTSTOPRORATECURSOR cursor local fast_forward
    for (select isnull(CREDITITEMID, ID), AMOUNT from #PRORATEDAMOUNTS);

    open AMOUNTSTOPRORATECURSOR;
    fetch next from AMOUNTSTOPRORATECURSOR into @ID, @WEIGHT;

    while @@FETCH_STATUS = 0 begin
        if @ORIGINALAMOUNT <> 0
            set @CURRENTAMOUNT = round((@WEIGHT / @ORIGINALAMOUNT) * @NEWAMOUNT, 2);
        else
            set @CURRENTAMOUNT = 0;

        update #PRORATEDAMOUNTS
        set AMOUNT = @CURRENTAMOUNT
        where CREDITITEMID = @ID or ID = @ID;

        set @NEWAMOUNT = @NEWAMOUNT - @CURRENTAMOUNT;
        set @ORIGINALAMOUNT = @ORIGINALAMOUNT - @WEIGHT;

        fetch next from AMOUNTSTOPRORATECURSOR into @ID, @WEIGHT;
    end

    close AMOUNTSTOPRORATECURSOR;
    deallocate AMOUNTSTOPRORATECURSOR;

    if @CREDITPAYMENTID is null
        select AMOUNT, ID, CREDITITEMID from #PRORATEDAMOUNTS;
    else
        select AMOUNT, ID, CREDITITEMID, @CREDITPAYMENTID from #PRORATEDAMOUNTS;
end