locked
Rows deleted RRS feed

  • Question

  • Guys

    I am running a batched delete using a while loop.  My question is, how do i get a total number of rows deleted from the delete statment ran on table 1?  As you can see from my example query below i have attempted this by running a select on the @rowcount variable, when executed this variable returns NULL.

    DECLARE @rowcount INT 
    
    WHILE EXISTS (SELECT top 1 * FROM Table2)
    BEGIN
    	DELETE FROM   Table1
    	WHERE ID IN (SELECT top 10 id FROM    Table2) 
    		
    	SET @rowcount = @rowcount + @@ROWCOUNT
    		
    	DELETE top (10) FROM   Table2
    END
    
    SELECT @rowcount AS rowsdeleted

    nuddy
    Friday, September 4, 2009 4:46 AM

Answers

All replies

  • Initialize @rowcount first:
     
    SET @rowcount=0


    Varsham Papikian, New England SQL Server User Group Executive Board, USA
    New England SQL Server User Group; My LinkedIn page
    Please remember to click the "Mark as Answer" button if a post helps you!
    • Marked as answer by Andy Fordyce Friday, September 4, 2009 5:04 AM
    Friday, September 4, 2009 4:55 AM
  • That did the trick.

    Many Thanks
    nuddy
    Friday, September 4, 2009 5:04 AM
  • I think the problem was coming from init of @rowcount was null implicitly.  Null in any expression is null.  I don't use @@rowcount much but I'd try to wrap that with an isnull function just to make sure you don't get a null on the delete from table1 and wipe out your @rowcount variable because of it.

    Try this:

    DECLARE @rowcount INT
    --INIT VARIABLE 
    SET @ROWCOUNT = 0
    WHILE EXISTS (SELECT top 1 * FROM Table2)
    BEGIN
    	DELETE FROM   Table1
    	WHERE ID IN (SELECT top 10 id FROM    Table2) 
    	--USE ISNULL JUST INCASE	
    	SET @rowcount = @rowcount + ISNULL(@@ROWCOUNT,0)
    		
    	DELETE top (10) FROM   Table2
    END
    
    SELECT @rowcount AS rowsdeleted

    EDIT: Sorry for the late post.

    • Proposed as answer by Jon Kleinhans Friday, September 4, 2009 5:06 AM
    Friday, September 4, 2009 5:05 AM
  • Scooby_0321,
    I think @@rowcount never returns NULL. The only possible exception may be the case when the number of affected rows is more than 2 billion (!) - that's what BOL says. I don't think we will face that limitation soon :-) 
    If we do, BOL recommends using rowcount_big() function which returns BIGINT.


    Varsham Papikian, New England SQL Server User Group Executive Board, USA
    New England SQL Server User Group; My LinkedIn page
    Please remember to click the "Mark as Answer" button if a post helps you!
    Friday, September 4, 2009 5:43 AM