SQL Print Message During Execution

PRINT statement is often used to return user defined message. It gives user some indication and help tracking multiple query statement for troubleshooting. However, PRINT statement may not be sufficient if we require immediate return message during an execution of a long running query. There is an alternative way.

Lets first examine the PRINT statement,

DECLARE @Count int = 1;

WHILE @Count <= 10
BEGIN
 WAITFOR DELAY '00:00:02';
 
 PRINT 'Running statement.. ' + CAST(@Count AS varchar);
 
 SET @Count = @Count + 1;
END

In this example, the list of messages only returned after 20 seconds. If we only wants to see the list of results after execution, print works well. However, if we require some messages to be returned during the execution, we could make use of RAISERROR statement. RAISERROR return an error message or user defined message. In order to prevent the user message in TRY block to be transferred to an associated CATCH block (for error handling), the severity of 10 or lower is specified. RAISERROR statement has a NOWAIT option to send message to the client immediately.

DECLARE @Count int = 1

WHILE @Count <= 10
BEGIN
 WAITFOR DELAY '00:00:02';
 
 RAISERROR (N'Running statement.. %d', 10, 1, @Count)
 WITH NOWAIT;
 
 SET @Count = @Count + 1;
END

Using RAISERROR with NOWAIT option returns message every 2 second during the execution in the above example. Note that the %d is a decimal variable for the count argument.