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 · Accepted 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

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

[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