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.

Answer 3

Can I just ask about the long term need for this facility - is it for debuging purposes?

If so, then you may want to consider using a proper debugger, such as the one found in Visual Studio, as this allows you to step through the procedure in a more controlled way, and avoids having to constantly add/remove PRINT statement from the procedure.

Just my opinion, but I prefer the debugger approach - for code and databases.