I have a slow-executing stored procedure that includes debugging and informational messages that I afterward print using the PRINT command. The messages show only after the procedure has finished running. Is there a quick way to print or flush this data before the procedure is done?
SQL Server sends data over its tabular data stream (TDS) in packet sizes that you can configure by changing the network packet size. By default, the packet size is 4KB, but you can set it as low as 512B. SQL Server sends the result set data when it has a full packet or when the batch is finished running. However, setting the packet size to 512B won't force an immediate output of your print commands, and it's certainly not optimal from a performance perspective. You can force your messages to come back immediately by using a RAISERROR statement and choosing the optional WITH NOWAIT keywords. You'll need to set a severity level and a state level. SQL Server treats a severity level of 10 as a user-defined informational message. The state level has no effect and can be any number from 1 to 127. The following command will have the same effect as a PRINT('This is an info message') command except that it will display the message immediately:
RAISERROR ('This is an info message.', 10, 1) WITH NOWAIT
The RAISERROR command supports parameter substitution, so you can do more complex information messages as well. See the SQL Server Books Online (BOL) RAISERROR topic for the full syntax of how to do that.