Results 1 to 2 of 2

Thread: delete problem

  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: delete problem

    hi all,
    can any one tell me why only 906 rows from the temp table are deleted by this sp:??
    Code:
             
    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    
    end

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    When 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •