Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FETCH NEXT RECORD Help

Status
Not open for further replies.

jgurgen

Programmer
Mar 9, 2006
192
US
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
 
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
 
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.

Code:
...
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?
 
The general FETCH NEXT loop I use is:

Code:
-- 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.
 
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()

 
Right, of course, working on more than one row is the essential reason for cursors. Does checking the fetch status solve the problem?
 
nope didnt do anyhting. im tracing the code now trying to see what i can
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top