Raising an Error to Print

Why do we print messages?

Printing messages is a common, well-known way of debugging code. This is useful to troubleshoot and identify where execution is currently taking place. PRINT is often used to debug code in SQL Server. We often see this typically used in stored procedures or deployment scripts.

An example using PRINT

Here’s an example of a “time-consuming” stored procedure using PRINT to return messages to the client. This is typically displayed in the “Messages” tab in SQL Server Management Studio (SSMS).

/*
Please note, CREATE OR ALTER is only available in SQL Server 2016 and higher.
*/
CREATE OR ALTER PROCEDURE dbo.usp_LongRunningProc
AS
    BEGIN;
        PRINT N'Starting procedure.';

        WAITFOR DELAY '00:00:01';

        PRINT N'Starting execution loop.';

        DECLARE @Start tinyint = 1;
        DECLARE @Stop tinyint = 5;
        DECLARE @IterationMsg nvarchar (32);

        WHILE @Start <= @Stop
            BEGIN;

                SET @IterationMsg = CONCAT(
                                           N'Executing iteration ', 
                                           CAST(@Start AS nchar (1)), 
                                           N'.'
                                          );

                WAITFOR DELAY '00:00:01';
                PRINT @IterationMsg;

                SET @Start += 1;
            END;
    END;

“WAITFOR DELAY” is used multiple times to emulate a longer-running process.

We can execute this procedure and watch the “Messages” tab.

EXEC dbo.usp_LongRunningProc;

The problem with using PRINT is that message output doesn’t appear immediately.

PRINT statements are buffered in SQL Server until it reaches the maximum packet size, meaning you typically won’t see messages until execution is complete. As a result, using PRINT this way is about as useful as watching the circle spin next to the “Executing query…” message on the bottom right-hand of our query window in SSMS.

What if we want to see these messages immediately?

This can be achieved by using RAISERROR() WITH NOWAIT. Yes, RAISERROR is one word and only has one “E.” Also, please note that “NOWAIT” is one word. Here’s the syntax for using RAISERROR:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,…n ] ] )
    [ WITH option [ ,…n ] ]

You may be wondering if raising an error could cause an issue. No worries, as long as we use a severity level between 0-10, it’s treated as an informational message.

An example using RAISERROR WITH NOWAIT

Here’s an example of the same stored procedure, replacing our previous PRINT messages with RAISERROR.

/*
Please note, CREATE OR ALTER is only available in SQL Server 2016 and higher.
*/
CREATE OR ALTER PROCEDURE dbo.usp_LongRunningProc
AS
    BEGIN;
        RAISERROR(N'Starting procedure.', 0, 1) WITH NOWAIT;

        WAITFOR DELAY '00:00:01';

        RAISERROR(N'Starting execution loop.', 0, 1) WITH NOWAIT;

        DECLARE @Start tinyint = 1;
        DECLARE @Stop tinyint = 5;
        DECLARE @IterationMsg nvarchar (32);

        WHILE @Start <= @Stop
            BEGIN;

                SET @IterationMsg = CONCAT(
                                           N'Executing iteration ', 
                                           CAST(@Start AS nchar (1)), 
                                           N'.'
                                          );

                WAITFOR DELAY '00:00:01';
                RAISERROR(@IterationMsg, 0, 1) WITH NOWAIT;

                SET @Start += 1;
            END;
    END;

Once again, let’s execute this procedure and watch the “Messages” tab.

EXEC dbo.usp_LongRunningProc;

You’ll notice the messages now display immediately while the procedure is executing. This is much more useful for troubleshooting and debugging procedures.

Using RAISERROR() WITH NOWAIT is becoming more common to achieve “instant prints.” I first noticed this pattern in some of Brent Ozar’s stored procedures in his First Responder Kit. I’m certainly not the first person to blog about this, I found many other posts on the topic, including:

There are many more great posts out there on the topic! I hope you learned something if you weren’t aware of how this worked. Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *