Sql Server print affected rows during while loop

I have a stored procedure running on sql server 2008 looping over a table of ~50 million rows, deleting records one day at a time (approx. 25,000 records a day). I want to be able to watch this occur as the process runs via print statements to the messages window. I'd like to be able to see a message each time a day's worth of deletions is committed. Is there any way to do something like this? The pr开发者_开发问答ocedure is roughly laid out like this:

WHILE EXISTS(<query statement>)
BEGIN

BEGIN TRAN

DELETE
FROM  <table>
WHERE <condition>
AND <condition>

--need some sort of "rows affected" statement here

COMMIT

END

---

Unfortunately, PRINT statement output is spooled up and not written to the output immediately. A way round this is to use RAISERROR like this:

RAISERROR ('MY MESSAGE', 0, 1) WITH NOWAIT

Specifically for what you want:

    DECLARE @Msg VARCHAR(200)

    WHILE EXISTS(<query statement>)
    BEGIN
    BEGIN TRAN

    DELETE
    FROM  <table>
    WHERE <condition>
    AND <condition>

    SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
    RAISERROR (@Msg, 0, 1) WITH NOWAIT

    COMMIT
    END

I also tend to include the current time in the message so I can record progress against time.

---

Anything more complex than select @@rowcount ?

---

How about using the OUTPUT clause?? Can you write to the SQL Server console from your stored proc??

Like in a trigger, you have a "Deleted" pseudo-table at your hands, which contains the rows that are being deleted, with all their columns.

WHILE EXISTS(<query statement>)
BEGIN
  BEGIN TRAN

  DELETE FROM  <table>
  OUTPUT deleted.ID, deleted.TimeStamp -- or whatever
  WHERE <condition>
  AND <condition>

  COMMIT

END

---

I used to do something similar to this, and because prints where spooled up it was not much use, however I discovered that the command line utility osql outputs them immediately, so I wrote the stored proc and called it from a batch file instead, which let me see the progress.

---

you could do something like this to iterate over each row

           declare @ii (id int) table 

     -- for batchchunk operations      

          declare @numTransactions as int = 0,          
            @totalRowsInserted as int = 0,          
            @chunksize int = 0,          
            @printnow varchar(1000)      


      --does the initial insert into the  table       
            select * from #table       
            SET @numTransactions =@@rowcount       
            SET @printnow = 'Should Insert :'+cast(@numTransactions as varchar)+ ' rows into the  table.'          
            exec db.printNow @printnow;       -- shortened proc

           BEGIN           
           INSERTCHUNKS:          

           SET ROWCOUNT @Batchsize                 



          insert into table                   
          (                  
            type_id,                  
            parent_id,                  
            created_by_user_id,                  
            created_at,                  
            updated_by_user_id,                  
            updated_at,                  
            created_by_system_id,                  
            updated_by_system_id,                  
            is_deleted                   
           )                  
          output inserted.id into @ii(id)                  

           select                  
          1,--party_type_id                  
         NULL,--parent_party_id                  
         @user_id, --created_by_user_id                  
         case when created_at is NULL then getdate() else created_at end ,                               

         NULL , --updated_by_user_id                  
         case when updated_at is NULL then getdate() else updated_at end, --
         updated_at                  
         case when created_by_system_id is null then 292 else 0 end, --created_by_system_id                  
          updated_by_system_id,--updated_by_system_id                   \
           0 --is_deleted                  
         from                   
         #table         
         order by id asc            
         OFFSET            
         @TotalRowsInserted ROWS           
         FETCH NEXT @batchsize ROWS ONLY       
         set @chunksize =@@rowcount             
          IF @chunksize > 0      
          BEGIN             
         set @totalRowsInserted = @totalRowsInserted + @chunksize             
          SET @printnow = 'Batch done: ' +
            cast(@chunksize as varchar) + ' rows in Batch ; '          
            + cast(@totalRowsInserted as varchar) + ' total inserted so far 
             into the table.'             exec db.printnow @printnow;            

           waitfor delay '00:00:01'          
          GOTO INSERTCHUNKS           
          END        
          SET ROWCOUNT 0;      
          END

---

Do the following:

DECLARE @queryCount AS INT, @start AS INT, @stop as INT, @table as varchar(max)

SELECT @start = cnt(*) as a FROM <table>

SELECT @queryCount = cnt(*)
    FROM  <table>
    WHERE <condition>
    AND <condition>

DELETE
    FROM  <table>
    WHERE <condition>
    AND <condition>

SELECT @stop = cnt(*) as a FROM <table>

PRINT(convert(varchar(max), @queryCount) + ' rows deleted from ' + convert(varchar(max), @table))

If you want to validate, you can throw in the following code.

PRINT('Rows at start: ' + convert(varchar(max), @start))
PRINT('Rows at end: ' + convert(varchar(max), @stop))
PRINT('Row difference: ' + convert(varchar(max), @start - @stop))

Caveat: If the database can affect rows while this is going on, you may see discrepancies.