Report progress from a batch/script/stored procedure

There are situations when you have to update/delete
high volume of data in a table(s).For example, moving
data to another database (archiving data).

Probably you will do it by chunks, by using some loop
in order not to hurt the system(in our case production).

In such cases it will helpful to see if you batch/SP did not
stuck, like you see % of completeness when you do backup.

You can use PRINT to report progress, but the PRINT
not sends the message immediately to the client.
Anything sent to PRINT will be buffered, and not released
until the buffer is full, or the query completes.
This buffer is around 8KB in size.

We will use RAISERROR with severity 10 and NOWAIT
option.If the severity from 0 to 10, the SQL Server do
not raise an error, it returns a plan message.

RAISERROR('ProgressMessage',10,25) WITH NOWAIT

Let`s take a simple example where we want to see how many
rows were affected (it can be counter of more than one
operation) in each iteration:

DECLARE @RowsUpdated int,
                    @ProgressMessage varchar(50)

SET @RowsUpdated = 1
SET @ProgressMessage=''

WHILE @RowsUpdated>0
BEGIN
      

      'you DML statement(s)' 

      SET @RowsUpdated = @@ROWCOUNT
      SET @ProgressMessage= CAST(@RowsUpdated as VARCHAR(50))

      RAISERROR(@ProgressMessage,10,25) WITH NOWAIT

END

Thanks for reading

Tweet