How to get the last iteration in cursor prematurely?

Question

How to get the last iteration in cursor prematurely to compare it with value and break if it meets the condition .

OPEN lcr_trans   
    FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
    WHILE @@FETCH_STATUS = 0   
    BEGIN
    IF(cast(@shift_start_time as time)<>'00:00:00.0000000'  AND (cast(@shift_end_time as time)<>'00:00:00.0000000' )  )
    BEGIN

      SELECT  TOP 1 @CompareWeekend= transtime_out from overtime where emp_num = @emp_num and trans_date = @previous_date;

      IF(@CompareWeekend  = @trans_time)
        BEGIN
            BREAK;
        END
    END
    -- The Cursor --
    --   ----     --
        FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
    END 
    close lcr_trans;
    DEALLOCATE lcr_trans; 

I want the last @trans_time in the iteration to compare with my variable .@CompareWeekend

Answer 1

try this:

declare @last_trans_time datetime, @last_machine_i varchar(max), @last_trans_camp_code varchar(max);
    OPEN lcr_trans   
        FETCH LAST FROM lcr_trans INTO @last_trans_time , @last_machine_i , @last_trans_camp_code;

        FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
        WHILE @@FETCH_STATUS = 0   
        BEGIN
           IF(cast(@shift_start_time as time)<>'00:00:00.0000000'  AND (cast(@shift_end_time as time)<>'00:00:00.0000000' )  )
           BEGIN

             SELECT  TOP 1 @CompareWeekend= transtime_out from overtime where emp_num = @emp_num and trans_date = @previous_date;

             IF(@CompareWeekend  = @trans_time)
             BEGIN
               BREAK;
             END
           END
           FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
        END 
   CLOSE lcr_trans;
   DEALLOCATE lcr_trans; 

you can read more about cursors here: https://msdn.microsoft.com/en-us/library/ms180152.aspx

Answer 2

You can get the last row in the cursor with the LAST keyword (source):

FETCH LAST FROM lcr_trans INTO @trans_time , @machine_i , @trans_camp_code;