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
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