hi all,
can any one tell me why only 906 rows from the temp table are deleted by this sp:??
         
create PROCEDURE usp_DelAllPersonalContacts11      
AS      
begin      
declare @.contactid int    ,@.tempid int  ,@.b int
create table #temp (tempid int identity(1,1),contactid int)      
set @.b=2000
while @.b>0
begin
insert into #temp (contactid)
values (@.b)
set @.b=@.b-1
end
select * from #temp  
declare user_cursor cursor for (select tempid,contactid from #temp )    
open user_cursor    
    
fetch next from user_cursor    
into @.tempid,@.contactid    
    
while @.@.fetch_status=0    
begin    
  delete from #temp where contactid=@.contactid    
print @.contactid
 fetch next from user_Cursor    
 into @.tempid,@.contactid    
end    
close user_cursor    
deallocate user_cursor    
select * from #temp
drop table #temp    
endWhen I change it into:
  delete from #temp where contactid <= @.contactid    
  if @.@.rowcount <> 1
  begin
    select 'RC: ', @.@.rowcount
    print @.contactid
  end
All rows are deleted, @.@.rowcount <> 1 fires once on @.contactid 2000.
But: my guess is that since the cursor has no order by clause it somewhat randomly puts a hold on a next row (whatever that means):
declare user_cursor cursor for
select tempid,contactid
from #temp
order by tempid
works for me with the contactid = @.contactid
 
No comments:
Post a Comment