When executing long-running SQL commands, it is often important to get feedback on the operations being performed.
In the context of SQL Server, the PRINT statement offers the ability to output basic informational messages that can help monitor progress and debugging.
While the PRINT statement provides a simple means of outputting a message, it can be somewhat limiting as it does not always output messages immediately. This means that messages are often not visible to the client until the overall SQL command has been completed.
If you are looking for a way to overcome this limitation, read on!
PRINT limitations
I think that one of the simplest ways to demonstrate the limitations of the PRINT statement is with the following SQL.
PRINT 'Processing...'; -- Simulate a long-running process. WAITFOR DELAY '00:00:05'; PRINT 'Processing completed.';
Upon reviewing the above SQL, you would likely expect that the message ‘Processing…’ would be immediately output. Then, there would be a delay of 5 seconds while a simulated long-running process is in progress, and finally, the text ‘Processing completed.’ would be displayed.
However, in reality, you’ll likely find that there will be a delay of 5 seconds, followed by both the ‘Processing…’ and ‘Processing completed.’ messages being output at the end, following the delay.
You can imagine that, in the case of a more complex SQL script or stored procedure that has multiple batch operations or sub-procedures, outputting the progress of each step would be very useful for monitoring and debugging. However, if all of these progress updates were not output until the end of the process, the messages would not be of any benefit.
Overcoming limitations
To overcome the inherent limitations of the PRINT statement, we can swap things over to use the RAISERROR statement instead (yes, it in fact named RAISERROR, not RAISEERROR!)
Here is the equivalent SQL that we used in the previous section, but this time using RAISERROR instead of PRINT.
RAISERROR('Processing...', 0, 1) WITH NOWAIT; -- Simulate a long-running process. WAITFOR DELAY '00:00:05'; RAISERROR('Processing completed.', 0, 1) WITH NOWAIT;
The first parameter of RAISERROR is the message that will be output.
The second parameter is the severity level. By setting the severity level to zero we are indicating that although we are calling RAISERROR, this is actually just an informational-level message. A severity level greater than 10 will cause an error to be raised, resulting in control being transferred to the CATCH block when the code is contained within a TRY block.
The third parameter is the state. This can be an integer between 0 and 255 and is intended for cases where you need to identify which part of your code the ‘error’ was raised from. In our case, it doesn’t matter so we are defaulting it to 1.
WITH NOWAIT is the key part of the code that causes the message to be output immediately to the client.
Neatening things up
While it’s nice that RAISERROR solves the limitations of PRINT in relation to buffering the output, it doesn’t look very intuitive to see the text RAISERROR scattered throughout our code when we’re using it to print messages rather than raise errors.
To neaten things up, we can wrap the RAISERROR code within a stored procedure, as shown below.
CREATE OR ALTER PROCEDURE dbo.WriteOutput @Message NVARCHAR(MAX) AS BEGIN -- Call RAISEERROR with level 0 to indicate an informational message. -- Use NOWAIT to send the message immediately to the output. RAISERROR(@Message, 0, 1) WITH NOWAIT; END
The stored procedure shown above accepts an @Message parameter which is in turn passed to RAISERROR.
Note that the naming of the WriteOutput procedure is inspired by the Write-Output PowerShell command. However, you may wish to choose a different name depending on your preferences.
We can update our previous SQL example for a third time as follows.
EXEC WriteOutput 'Processing...'; -- Simulate a long-running process. WAITFOR DELAY '00:00:05'; EXEC WriteOutput 'Processing completed.';
I’m sure you’ll agree that this is much more natural to read compared to calling RAISERROR directly.
One thing to consider with the stored procedure approach is that you’ll not be able to avail of the “C printf function style formatting” functionality that RAISERROR provides. If you want to pass a concatenated string to the stored procedure, you’ll need to define a variable and pass it as the @Message parameter, as shown below.
DECLARE @Message VARCHAR(MAX) = 'Processing started at: ' + FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); EXEC WriteOutput @Message; -- Simulate a long-running process. WAITFOR DELAY '00:00:05'; SET @Message = 'Processing completed at: ' + FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); EXEC WriteOutput @Message;
In most cases, you’ll probably not need the advanced formatting functionality, so usually, it shouldn’t be an issue.
Summary
In this article, I explained the limitations of the SQL Server PRINT statement and demonstrated how the RAISERROR statement can be used instead to send messages immediately to the client.
I showed how you can wrap this functionality in a stored procedure to help make your SQL code read more naturally.
Lastly, I demonstrated how you can pass messages that are built up from concatenated strings using a SQL Server variable that is passed along to the stored procedure.
Comments