FETCH NEXT RECORD Help

[jgurgen]

i have a stored procedure that is fetching the next record in a cursor and checking its value. Problem is that on the last record its looking to fetch the next record but there is nothing in there. How can i check if the next record doesnt exist?

Not the best coding


FETCH NEXT FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- Fetch first record
WHILE @@FETCH_STATUS = 0
BEGIN -- BEGINS While loop
IF(@DPO IS NOT NULL AND @DPO <= @CA1) -- DatePaidOff exists and its before the closing actual - Loan was paidoff before closing - Use DPO as last possible transactiondate
BEGIN
SET @Prior_HC = @HC
IF(@TD <= @DPO) -- TransactionDate of current payment is <= DatePaidOff
BEGIN
FETCH NEXT FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH NEXT RECORD
UPDATE iSplit_BuyAmount_DaysOpen SET EndTransactionDate = @TD WHERE LoanID=@ID AND HistoryCounter=@Prior_HC -- Update Statement - UPDATES PRIOR RECORD
-- FETCH PRIOR FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH PRIOR RECORD
END -- END IF

ELSE -- ELSE ClosingActual is > DatePaidOff - SET EndTranDate = TranDate
BEGIN -- BEGIN ELSE
UPDATE iSplit_BuyAmount_DaysOpen SET EndTransactionDate = @TD WHERE LoanID = @ID and HistoryCounter = @HC -- Update Statement - UPDATES CURRENT RECORD
FETCH NEXT FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH NEXT RECORD
END -- END ELSE
END -- END IF

ELSE -- DatePaidOff IS NULL
BEGIN -- BEGIN ELSE
SET @Prior_HC = @HC -- SET @Prior_HC to current HistoryCounter
IF(@TD <= @CA1) -- TransactionDate of current payment is <= ClosingActual
BEGIN


-- ERROR BEGINS

FETCH NEXT FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH NEXT RECORD

-- ERROR ENDS
[/color red]

IF(@TD > @CA1) -- IF TRANSACTIONDATE IS GREATER THAN THE CLOSINGACTUAL DATE -
BEGIN -- BEGIN IF
UPDATE iSplit_BuyAmount_DaysOpen SET EndTransactionDate = @CA1 WHERE LoanID=@ID AND HistoryCounter=@Prior_HC -- Update Statement - UPDATES PRIOR RECORD @CA1
END -- END IF

ELSE -- ELSE
BEGIN -- BEGIN ELSE
UPDATE iSplit_BuyAmount_DaysOpen SET EndTransactionDate = @TD WHERE LoanID=@ID AND HistoryCounter=@Prior_HC -- Update Statement - UPDATES PRIOR RECORD @TD
-- FETCH PRIOR FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH PRIOR RECORD
END -- END ELSE
END -- END IF

ELSE -- ELSE TransactionDate is > ClosingActual - SET EndTranDate = TranDate
BEGIN -- BEGIN ELSE
UPDATE iSplit_BuyAmount_DaysOpen SET EndTransactionDate = @TD WHERE LoanID = @ID and HistoryCounter = @HC -- Update Statement - UPDATES CURRENT RECORD
FETCH NEXT FROM Date_Cursor INTO @ID, @HC, @TD, @ETD, @BAP, @DPO, @CA2, @CE2 -- FETCH NEXT RECORD
END -- END ELSE

END -- END ELSE
END -- END While loop

 

[hmckillop]

Check the @@FETCH_STATUS as you do in the WHILE Loop.
I am sure there is a better way of doing the whole thing rather than cursors.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[rac2]

It is pretty difficult for me to follow the process in your code so I cant give you the exact answer.

But the short answer may be that you should check the cursor status after every FETCH NEXT. If there is no next row then break out of the WHILE loop.

...
IF @@FETCH_STATUS = 0
  BEGIN
    /*perform this step*/
  END
ELSE BREAK

Another thought. Can you revise the flow so that you only do one FETCH NEXT within the WHILE loop?

 

[johnscott76]

The general FETCH NEXT loop I use is:

-- Load the first record
FETCH NEXT FROM @Cursor INTO @Fields

-- Set up the loop, this will loop through your data set until you run out of records
WHILE (@@FETCH_STATUS = 0) BEGIN
  -- The meat & potatoes of the loop

  -- Grab the next record
  FETCH NEXT FROM @Cursor INTO @Fields
END

The first FETCH NEXT will load the first record, and if there isn't one the WHILE loop condition is true from the start, meaning it skips processing entirely. From that point forward it will loop through the record set and once you process the last record, exits gracefully.

 

[jgurgen]

the reason i use multiple FETCH NEXT is because in the while loop i load the first record
store its counter in a var
goto the next record
update the first record with values from the next record
then run loop again.

EXAMPLE

1 1/1/07 1/5/07
2 1/5/07 1/10/07
3 1/10/07 1/14/07
4 1/14/07 1/27/07
5 1/27/07 GETDATE()

 

[rac2]

Right, of course, working on more than one row is the essential reason for cursors. Does checking the fetch status solve the problem?

 

[jgurgen]

nope didnt do anyhting. im tracing the code now trying to see what i can

 

[jgurgen]

ok i got understand it now. I added a print after that FETCH and found that because the last record was already FETCHED that when it went to FETCH the next record it wouldnt return @@FETCH_STATUS = -2. What it does is returns the same row. Why i dont know.

So i just had to check if the Primkeys were the same after the fetch and it worked.

THanks for all the help everyone. Hope this helps you too.

 

[rac2]

Is it possible that the last FETCH NEXT that returned the same row was performed after a FETCH PRIOR?

Cause I believe that when the cursor is postioned on ( actually just after) the last row, a FETCH NEXT will return a non-zero status code. That is the main purpose of @@FETCH_STATUS.