Problems with cursor and update where current of

I'm having a problem ... obviously or I wouldn't be here. Anyway ... There is a table containing a line number field (called ln_num) that needs to be renumbered based on the order of other certain fields for certain accounts. The table in question is called parcel_rel and here are the fields:

strap varchar(13); ln_num int; tp varchar(2); rel_strap varchar(13)

PKey is on strap, ln_num

I load into a "temp" table the accounts that need renumbering (called renum_parcel_rel_table) and on this particular run, I have only one: strap = 0935310000111, which has two records (one with tp=TB and another with tp=PA. Here is the code that I am using. It ran PERFECTLY on SQL2000, but since we upgraded to SQL2005, it gets caught in a loop -- thus the reason for me posting this question.

set nocount on

declare @.strap varchar (13) ,
@.old_strap varchar (13) ,
@.ln_num int ,
@.c_ln_num int ,
@.tp varchar (10) ,
@.rel_strap varchar (13)

declare cRecords__parcel_rel cursor for
select r.strap, ln_num, tp, rel_strap
from r_2006..parcel_rel r
join h_2006..renum_parcel_rel_table h
on r.strap = h.strap
order by r.strap, tp, rel_strap
for update of ln_num

set @.old_strap = ''

open cRecords__parcel_rel
fetch next from cRecords__parcel_rel into @.strap, @.c_ln_num, @.tp, @.rel_strap

while (@.@.FETCH_STATUS = 0) begin

print 'pre-update: @.strap = ' + @.strap + ' @.c_ln_num = ' + cast(@.c_ln_num as varchar(10)) + ' @.tp = ' + @.tp + ' @.rel_strap = ' + @.rel_strap
if @.strap = @.old_strap begin
set @.ln_num = @.ln_num + 1
end else begin
set @.ln_num = 1
set @.old_strap = @.strap
end
update r_2006..parcel_rel
set ln_num = @.ln_num
where current of cRecords__parcel_rel -- key to it all!!!!!

print 'updated ln_num to: @.ln_num = ' + cast(@.ln_num as varchar(10))
fetch next from cRecords__parcel_rel into @.strap, @.c_ln_num, @.tp, @.rel_strap
print '@.@.FETCH_STATUS = ' + cast(@.@.FETCH_STATUS as varchar(10))
end

close cRecords__parcel_rel
deallocate cRecords__parcel_rel

set nocount off

The problem is that I get output like this and the loop never dies (@.@.fetch_status bolded for reference):

pre-update: @.strap = 0935310000111 @.c_ln_num = 1661 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 1
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1662 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 2
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 3
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 2 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 4
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 3 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 5
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 4 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 6
@.@.FETCH_STATUS = 0
.... etc

As you can see, even though this account has only two entries in the parcel_rel table (one tp=PA and one tp=TB) the cursor keeps getting a @.@.fetch_status = 0 even after the last (second) record is passed.

Interestingly enough, if I remove (comment out) the update...where current of portion of the code, look:

pre-update: @.strap = 0935310000111 @.c_ln_num = 1883 @.tp = PA @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 1
@.@.FETCH_STATUS = 0
pre-update: @.strap = 0935310000111 @.c_ln_num = 1884 @.tp = TB @.rel_strap = 0935310000110
updated ln_num to: @.ln_num = 2
@.@.FETCH_STATUS = -1

Notice that after the second record is read in and processed, and the following fetch next is executed, @.@.FETCH_STATUS correctly returns a -1. Is the update...where current of causing the cursor to revert back to the first record, or causing the following fetch next to function like a fetch first? I'm puzzled that this functionality would so greatly change between SQL2000 and SQL2005.

Any comments would be greatly appreciated. And if you could also email replies or notification of replies to bscrivener@.hcad.org I would greatly appreciate that, too.

Thanks in advance!

Your cursor problem (other than using a cursor :)) is probably due to the cursor not being static. I think changing to a fast_forward or static cursor will fix your problem. However, it would be far easier to use the new row_number function to do the reorder. Here is a sample:

create table orderMe
(
orderMeId int primary key,
groupName varchar(10),
value varchar(20),
orderBy int,
unique(groupName, value)
)
insert into orderMe
select 1,'beatles','john',10
union all
select 2,'beatles','paul',12
union all
select 3,'beatles','george',12
union all
select 4,'beatles','ringo',12
union all
select 5,'rat pack','frank',1
union all
select 6,'rat pack','sam',50
union all
select 7,'rat pack','dean',1
go
update orderMe
set orderBy = orderUs.newOrderMeId
from orderMe
join (select orderMeId,
row_number() over (partition by groupName order by value) as newOrderMeId
from orderMe) as orderUs
on orderMe.orderMeId = orderUs.orderMeId
go
select *
from orderMe

Now the orderBy column is order by their name... Much easier than a cursor (and likely a lot faster)

orderMeId groupName value orderBy
-- - -- --
1 beatles john 2
2 beatles paul 3
3 beatles george 1
4 beatles ringo 4
5 rat pack frank 2
6 rat pack sam 3
7 rat pack dean 1

|||

You can simplify the UPDATE statement by using a CTE like:

with o
as
(
select orderBy, row_number() over (partition by groupName order by value) as newOrderMeId
from orderMe
)
update o
set orderBy =newOrderMeId;

|||You gotta admire the work. Nice.|||Oooh, cool. Very nice...