How to use the PRINT statement to track execution as stored procedure is running?

Question

Reference: SQL Server

I have a stored procedure with a while loop in it and I want some messages to be printed after every 500 loops.

So, I've written -

CREATE spxxx
AS
BEGIN
    BEGIN TRAN
        DECLARE @counter = 0;

        WHILE <somecondition>
            SET @counter = @counter + 1;

            IF @counter % 50 = 0
            BEGIN
                PRINT @counter;
            END
        END
     COMMIT TRAN
END -- End spxxx

But it prints all the messages once the proc ends. I want it to print the messages while its running.

Answer 1

I'm sure you can use RAISERROR ... WITH NOWAIT

If you use severity 10 it's not an error. This also provides some handy formatting eg %s, %i and you can use state too to track where you are.

Answer 2

SQL Server returns messages after a batch of statements has been executed. Normally, you'd use SQL GO to indicate the end of a batch and to retrieve the results:

PRINT '1' GO  WAITFOR DELAY '00:00:05'  PRINT '2' GO  WAITFOR DELAY '00:00:05'  PRINT '3' GO 

In this case, however, the print statement you want returned immediately is in the middle of a loop, so the print statements cannot be in their own batch. The only command I know of that will return in the middle of a batch is RAISERROR (...) WITH NOWAIT, which gbn has provided as an answer as I type this.