Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 procedure 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
like image 695
Kevin Avatar asked Jan 15 '10 14:01

Kevin


2 Answers

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.

like image 74
AdaTheDev Avatar answered Sep 28 '22 16:09

AdaTheDev


Anything more complex than select @@rowcount ?

like image 24
Andrew Avatar answered Sep 28 '22 16:09

Andrew