Home » Blog » PRINT vs. RAISERROR

PRINT vs. RAISERROR

2012-05-27 - General, T-SQL Statements

Introduction

Did you know that the RAISERROR command can be used as a powerful PRINT alternative?
In this article I am going to compare the two and show the advantages that RAISERROR offers over PRINT.

Print Lags

One of the bigger disadvantages of PRINT is its output buffering behavior. Let's look at an example to clarify what I mean by that:

[sql] DECLARE @c INT;SET @c = 1;
WHILE(@c<100)
BEGIN
PRINT @c;
SET @c = @c + 1;
WAITFOR DELAY '00:00:00.2'
END
[/sql]

This loop prints out an increasing counter value, waiting for 0.2 seconds after each print. It runs for about 40 iterations before any output is generated. Then it waits for another ~40 iterations before the next block of output is returned to the client, and so on. You can observe this behavior in the following video:

SQL Server does not allow for any interactivity within batches, so printing is the only feedback mechanism available. In almost all cases it is used to inform about the current state of the execution which might even include warnings. This feedback mechanism is of reduced value, if you can't rely on it happening in real-time.

Real-Time RAISERROR

In its standard form, RAISERROR shows the same buffering behavior. However, there is a little known option NOWAIT that causes the output buffer to get flushed immediately. That makes it usable for real-time feedback:

The disadvantage of using RAISERROR in this way is that it outputs an additional error information line with each call and that at least in Management Studio the output is displayed in red. This can make it hard to find the actual information in all the output clutter. But there is a way to get rid of that too.

The Printing RAISERROR

When RAISERROR is called with a severity between 1 and 9, the output loses its red color but it still contains that extra line, only this time after the printed message instead of before. If you however call RAISERROR with a severity of 0 or 10 it behaves just like the print statement:

[sql] DECLARE @c INT;SET @c = 1;
WHILE(@c<10)
BEGIN
RAISERROR('test',0,1)WITH NOWAIT;
SET @c = @c + 1;
WAITFOR DELAY '00:00:01'
END
[/sql]

When you execute this code you get print behavior without print lag:

The curious 17

There is actually one exception to the buffering rule that you need to be aware of: When RAISERROR is executed with a severity of 17, the output is held in a separate output buffer and returned after the batch finishes execution, even if the NOWAIT option is specified.

To show this behavior, the following code alternatingly calls RAISERROR with a severity of 16 and 17.

[sql] DECLARE @c INT;SET @c = 0;
WHILE(@c<10)
BEGIN
DECLARE @s INT;SET @s = 16+@c%2;
DECLARE @m NVARCHAR(MAX); SET @m = CAST(@c AS NVARCHAR(MAX));
RAISERROR(@m,@s,10)WITH NOWAIT;
SET @c = @c + 1;
WAITFOR DELAY '00:00:01'
END
[/sql]

Executing above code produces this output:

The same behavior can also be observed when using a severity of 18.

Shortcomings

Now there is one disadvantage that I don't want to hide. While you can pass a variable to RAISERROR you cannot execute any calculations within its arguments. That includes something as simple as casting an integer to a string. PRINT on the other hand is able to take anything you throw at it:

To alleviate this a little, RAISERROR allows to use the C-style prinf syntax:

[sql] DECLARE @int INT;SET @int = -1-POWER(-2,31);
DECLARE @bigint BIGINT;SET @bigint = -1-POWER(CAST(-2 AS BIGINT),63);
DECLARE @nvarchar NVARCHAR(MAX);
SET @nvarchar = '{This is a string in braces!}';

RAISERROR('INT:%d, BIGINT:%I64d, string:%s',0,1,@int,@bigint,@nvarchar);
[/sql]

Executing the above batch produces this output:

This does not cover all cases, though. Printing out a timestamp using the SYSDATETIME() function for example requires you to cast the DATETIME2 value to a string. That cannot be done inside the call to RAISERROR so you have to store the result of that conversion in a VARCHAR variable and then pass that to the RAISERROR statement.

Besides of that, this substitution syntax is actually quite powerful. It allows specifying a fixed length for each substitution value. You can set a precision for numbers and you can request that a number gets displayed as hex- or octal-value. Have a look at the BOL entry for details: RAISERROR (Transact-SQL) .

Conclusion

While RAISERROR is not as flexible as PRINT when looking at possible parameter values, it has the great advantage that you can use it to control output buffer behavior. That makes it the preferred choice when the output is used to give feedback about the state of the currently executing SQL batch.