PRB: Dynamic Cursor Infinite Loop When a Non-Unique Clustered Index Key Is Updated to an Equal or Larger Value

View products that this article applies to.

This article was previously published under Q280406

Expand all | Collapse all

SYMPTOMS

A dynamic cursor goes into an infinite loop when a non-unique clustered index key is updated to a value greater than or equal to itself.

Back to the top

For a non-unique clustered index key update, if the new value is greater than or equal to the original value, the update actually deletes the original value and inserts the new value. This adds a new row, which is reflected into the dynamic cursor and causes the @@fetch_status to always return '0'.

Back to the top

WORKAROUND

Define the index as nonclustered instead of clustered.

Back to the top

MORE INFORMATION

This behavior is by design. The following example demonstrates the behavior.

Create table testtable(col1  int null, col2  int null, col3  int null )
go

Create clustered index clustindex on testtable  (col1,col2,col3) 
go

insert into testtable values(100,100, 100)
go

declare mycursor cursor dynamic
for 
select * from testtable
for update of col1,col2 ,col3
open mycursor 
declare @count integer
declare @f1 integer
declare @f2 integer
declare @f3 integer

fetch mycursor into @f1,@f2,@f3
select @count = 0
while @@fetch_status = 0
begin  
   update testtable
   set col1 = 300, col2 = 300, col3 = 300
   where current of mycursor 
   fetch mycursor into @f1,@f2,@f3
end

close mycursor 
deallocate mycursor 
go

Back to the top