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.