Cursor fetches less than expected records

[bansalhimanshu]

In the given code I am facing the following problem (steps given):
1) I update a number of records (10 in numbers here) with status as 'P' (to be processed)
2) I run a cursor for the record marked 'P'.
3) The cursor fetches (inner while loop) only 3 or 4 and then mark @@sqlstatus as 2. It marks only the fetched records with status 'S' or 'W' (which are later marked to 'F').
4) The cursor loop terminates. Processing only a few records of the set of records marked P.

Because of this most of my records get marked as 'P' while they should be all be marked either F (Failed) or S (Success).

The code is given below:-

create proc EQP_MIG_EVENT @run_mode char(1) = 'N' /* N-Normal mode, F-Failure mode */
as
declare
@_record_id char(4),
@_region_id char(4),
@_eqmt_num char(10),
@_eqmt_type char(1),
@_event_dt_loc datetime,
@_check_digit char(1),
@_curr_loca char(5),
@_load_empty_ind char(1),
@_empty_to_shipper_ind char(1),
@_ib_ob_ind char(1),
@_last_event_discharge_ind char(1),
@_doc_ref char(12),
@_doc_type char(2),
@_next_loca char(5),
@_cntr_cond char(2),
@_svc_loop char(4),
@_vsl_cde char(3),
@_voy_num char(3),
@_dir_bound char(5),
@_load_port char(3),
@_dsgh_port char(3),
@_seal_num char(12),
@_gross_wt char(5),
@_gross_wt_unit char(3),
@_stowage_num char(10),
@_chassis_num char(10),
@_chassis_cond char(2),
@_genset_num char(10),
@_genset_cond char(2),
@_remarks varchar(240),
@_lease_out_auth_num char(18),
@_upload_status char(1), /* S-Success, F-Failure, W-Will fail, P-Work in process blank-Untouched */
@_upload_status_remarks varchar(240),
@_rtn_status int,
@_pin char(8),
@_batch_records int,
@_any_more_record char(1) /* Y, N */

declare EQP_MIG_02_cur cursor for
select RECORD_ID, REGION_ID, EQMT_NUM, EQMT_TYPE,
EVENT_DT_LOC, CHECK_DIGIT, CURR_LOCA, LOAD_EMPTY_IND, EMPTY_TO_SHIPPER_IND,
IB_OB_IND, LAST_EVENT_DISCHARGE_IND, DOC_REF, DOC_TYPE, NEXT_LOCA,
CNTR_COND, SVC_LOOP, VSL_CDE, VOY_NUM, DIR_BOUND, LOAD_PORT, DSGH_PORT,
SEAL_NUM, GROSS_WT, GROSS_WT_UNIT, STOWAGE_NUM, CHASSIS_NUM, CHASSIS_COND,
GENSET_NUM, GENSET_COND, REMARKS, LEASE_OUT_AUTH_NUM, UPLOAD_STATUS
from EQP_MIG_EVENT_02
where UPLOAD_STATUS = 'P'

select @_pin = 'MIG_USER', @_batch_records = 10 /* Set this to 500 */

set rowcount @_batch_records
if @run_mode = 'N'
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'P' where UPLOAD_STATUS is null
else if @run_mode = 'F'
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'P' where UPLOAD_STATUS is null
or UPLOAD_STATUS = 'F'
if @@rowcount > 0
select @_any_more_record = 'Y'
else
select @_any_more_record = 'N'
set rowcount 0

while @_any_more_record = 'Y'
begin
open EQP_MIG_02_cur
fetch EQP_MIG_02_cur into @_record_id, @_region_id, @_eqmt_num,
@_eqmt_type, @_event_dt_loc, @_check_digit, @_curr_loca, @_load_empty_ind,
@_empty_to_shipper_ind, @_ib_ob_ind, @_last_event_discharge_ind, @_doc_ref, @_doc_type,
@_next_loca, @_cntr_cond, @_svc_loop, @_vsl_cde, @_voy_num, @_dir_bound, @_load_port,
@_dsgh_port, @_seal_num, @_gross_wt, @_gross_wt_unit, @_stowage_num, @_chassis_num,
@_chassis_cond, @_genset_num, @_genset_cond, @_remarks, @_lease_out_auth_num,
@_upload_status

while @@sqlstatus != 2
begin
select @_upload_status_remarks = null
if @_record_id in ('STOR', 'TRAN', 'LOAD')
begin
exec @_rtn_status = EQP_MIG_MOVEMENT_EVENT @_record_id, @_eqmt_num, @_eqmt_type,
@_event_dt_loc, @_check_digit, @_curr_loca, @_load_empty_ind,
@_empty_to_shipper_ind, @_ib_ob_ind, @_last_event_discharge_ind, @_doc_ref,
@_doc_type, @_next_loca, @_cntr_cond, @_svc_loop, @_vsl_cde, @_voy_num,
@_dir_bound, @_load_port, @_dsgh_port, @_seal_num, @_gross_wt, @_gross_wt_unit,
@_stowage_num, @_chassis_num, @_chassis_cond, @_genset_num, @_genset_cond,
@_remarks, @_pin, @_upload_status_remarks out
end
else if @_record_id in ('TTLC', 'LOUT')
begin
exec @_rtn_status = EQP_MIG_TERMINATION_EVENT @_record_id, @_eqmt_num, @_eqmt_type,
@_event_dt_loc, @_check_digit, @_curr_loca, @_lease_out_auth_num, @_pin,
@_upload_status_remarks out
end

if @_rtn_status = 0
begin
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'S',
UPLOAD_STATUS_REMARKS = case UPLOAD_STATUS_REMARKS
when null then @_upload_status_remarks
else rtrim(@_upload_status_remarks) + '/' + UPLOAD_STATUS_REMARKS end
where EQMT_NUM = @_eqmt_num
end
else
begin
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'W',
UPLOAD_STATUS_REMARKS = case UPLOAD_STATUS_REMARKS
when null then isnull(rtrim(@_upload_status_remarks), 'Unknown system error')
else isnull(rtrim(@_upload_status_remarks), 'Unknown system error') + '/' +
UPLOAD_STATUS_REMARKS end
where EQMT_NUM = @_eqmt_num
end

fetch EQP_MIG_02_cur into @_record_id, @_region_id, @_eqmt_num,
@_eqmt_type, @_event_dt_loc, @_check_digit, @_curr_loca, @_load_empty_ind,
@_empty_to_shipper_ind, @_ib_ob_ind, @_last_event_discharge_ind, @_doc_ref, @_doc_type,
@_next_loca, @_cntr_cond, @_svc_loop, @_vsl_cde, @_voy_num, @_dir_bound, @_load_port,
@_dsgh_port, @_seal_num, @_gross_wt, @_gross_wt_unit, @_stowage_num, @_chassis_num,
@_chassis_cond, @_genset_num, @_genset_cond, @_remarks, @_lease_out_auth_num,
@_upload_status
end
close EQP_MIG_02_cur

set rowcount @_batch_records
if @run_mode = 'N'
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'P' where UPLOAD_STATUS is null
else if @run_mode = 'F'
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'P' where UPLOAD_STATUS is null
or UPLOAD_STATUS = 'F'

if @@rowcount > 0
select @_any_more_record = 'Y'
else
select @_any_more_record = 'N'
set rowcount 0
end
deallocate cursor EQP_MIG_02_cur
update EQP_MIG_EVENT_02 set UPLOAD_STATUS = 'F' where UPLOAD_STATUS = 'W'

go

 

[bansalhimanshu]

This problem was coming because there was no unique index made on the table. When I made the unique index on the table then the cursor started fetching all the records.