Dear all,

I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected)
6255.84
(1 row(s) affected)
Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'

Can anyone kindly tell me how to fix the error? Thanks.

Here is my code:

DECLARE @lastprice real
DECLARE @updatedprice real
DECLARE @updatedRe real
DECLARE @updatedAUX real
SET @lastprice = (
    SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
    )
SET @updatedprice = @lastprice;
DECLARE date_cursor SCROLL CURSOR
FOR SELECT Re_adj from #ClosePrice ORDER BY DateTD;
OPEN date_cursor;
FETCH LAST FROM date_cursor INTO @updatedRe;
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @updatedprice = @updatedprice / (1 + @updatedRe);
    PRINT @updatedprice;
    FETCH PRIOR FROM date_cursor INTO @updatedRe;
    UPDATE #ClosePrice SET Close_P_adj = @updatedprice WHERE CURRENT OF date_cursor;
  END
CLOSE date_cursor;
DEALLOCATE date_cursor;
UPDATE #ClosePrice
SET #ClosePrice.Close_P_adj = @lastprice
WHERE #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
go

• Edited by Bobojin_Z 21 hours 44 minutes ago

I think you have to change the order as follows:

........
UPDATE #ClosePrice SET Close_P_adj = @updatedprice WHERE CURRENT OF date_cursor;
FETCH PRIOR FROM date_cursor INTO @updatedRe;
.......

Changing the order fixed the error, but the outcome is not what I expected:

I expect that the last column is shown as

2015-09-10    AG1512.SHF         3333    AG1512.SHF          3333    0.000300169    0.000300169    0.000300169    3333
2015-09-11    AG1512.SHF          3328    AG1512.SHF          3328    -0.00150013    -0.00150013    -0.00150013    3328
2015-09-14    AG1512.SHF          3302    AG1512.SHF          3302    -0.0078125    -0.0078125    -0.0078125    3302

The data on 911 is computed as 3302 / (1 + -0.0078125) = 3328

After I change the order, I found 3328 on 911 is missing, and the 3333 comes earlier:

2015-09-10    AG1512.SHF          3333    AG1512.SHF          3333    0.000300169    0.000300169    0.000300169    3332
2015-09-11    AG1512.SHF          3328    AG1512.SHF          3328    -0.00150013    -0.00150013    -0.00150013    3333
2015-09-14    AG1512.SHF          3302    AG1512.SHF          3302    -0.0078125    -0.0078125    -0.0078125    3302

Is there a way to modify WHILE @@FETCH_STATUS = 0  to stop the cursor earlier? Thanks a lot.

• Edited by Bobojin_Z 20 hours 57 minutes ago

In this case, your first logic was right. You have just to check the cursor:

 PRINT @updatedprice;
     FETCH PRIOR FROM date_cursor INTO @updatedRe;
     IF (@@FETCH_STATUS <> 0) BREAK;
     UPDATE #ClosePrice SET Close_P_adj = @updatedprice WHERE CURRENT OF date_cursor;

Great! It works. Thank you Berimi.