Friday, February 17, 2012

delete problem

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