SQL SERVER – PRINTing Status Using RAISERROR With NOWAIT Option

In this blog post we are going to learn about PRINTing Status Using RAISERROR With NOWAIT Option.

The Problem 

There are many situations where you want to know which line of code is completed in the batch. Mostly this is a series of commands in SSMS and we want to know where it has reached so far.

I have seen many using PRINT statement after each statement. But the problem with PRINT statement is that it is not to SSMS unless output buffer is full. I think the buffer size is around 8 KB.

Here is the example of what I mean. You can copy below code in SQL Server Management Studio and run it in text mode to see that output.

PRINT 'SQLAuthority'+ REPLICATE(' ',8000)
PRINT 'Pinal'+ REPLICATE(' ',116)
WAITFOR DELAY '00:00:05'
PRINT 'Final Message'

When we run the code, we can notice that SQLAuthority is printed as soon as we execute the whole code. The Print, which is there is next line, is not printed immediately. The output can only be seen after 5 seconds, when WAIT is complete. I have selected 116 because if I use 115 then both would not be printed because buffer 8K is not full.

Now imagine the same piece of code within a batch where we are doing an insert in tables and we want to know where exactly the code is executing. In that case, we can’t rely on print statement.

The Solution

Here is one of the solution of the above problem.  We can use RAISERROR.

DECLARE @msg1 varchar(max) = 'SQLAuthority'+ REPLICATE(' ',8000)
DECLARE @msg2 varchar(max) = 'Pinal'+ REPLICATE(' ',116)
DECLARE @msg3 varchar(max) ='Final Message'
RAISERROR (@msg1, 0, 1) WITH NOWAIT
RAISERROR (@msg2, 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR (@msg3, 0, 1) WITH NOWAIT

If we run above code, we can see that first two are printed immediately and then delay of 5 seconds and finally the last message is printed.

As we can see that the RAISERROR method is a much more sophisticated of returning status messages to the client instead of using PRINT.

Reference:Pinal Dave (http://blog.SQLAuthority.com)