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  lt somecondition gt              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

User · Answer

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.

User · Answer

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

User · Answer

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

[sql-server] How to use the PRINT statement to track execution as stored procedure is running?

Examples related to sql-server

Examples related to tsql