For years, I relied on using PRINT in my T-SQL to give me updates on my script’s progress in SQL Server Management Studio. After each major statement, I’d throw in a “PRINT ‘Starting duplicate record check…'” or something like that to tell me how far along it was.

It wasn’t until a few years ago, when I started contributing to the First Responder Kit at Brent Ozar Unlimited, that I noticed every status message in the kit scripts was thrown with something other than PRINT.

Strange, I thought, since those scripts like to report on what statements are running. Turns out, they avoided PRINT because it has some serious drawbacks:

• PRINT doesn’t necessarily output anything at the moment it’s called.
• PRINT statements won’t show up in Profiler.
• PRINT can’t be given variable information without CAST or CONVERT.

Let’s focus on the first drawback, because not printing right away defeats the purpose of using PRINT for status messages. It’s also our biggest problem with PRINT.

Take this example and run it in SSMS:

PRINT 'The number of the bus is 2525.' 
WAITFOR DELAY '00:00:05'; 

PRINT 'It''s running downtown from Venice.' 
WAITFOR DELAY '00:00:05';

Weird, right? You’d think at least the first PRINT statement would run immediately. Instead, both messages come after both WAITFOR commands. That’s nuts.

Pop Quiz, Hotshot

If SSMS is going to hold the PRINT statements hostage, what do you do?

You rescue the hostage with GO:

PRINT 'The number of the bus is 2525.'
GO
WAITFOR DELAY '00:00:05';

PRINT 'It''s running downtown from Venice.'
GO
WAITFOR DELAY '00:00:05';

This will print the messages as soon as the statements run. Great!

Uh-oh:

“Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.”

That’s right — if you’re using any local variables in your script, like maybe an integer @i that’s counting iterations, that variable is toast as soon as you hit GO.

In other words, you’ve shot the hostage.

RAISERROR to the Rescue

Let’s try our query again, only this time we’ll use RAISERROR. (Don’t ask me where the other ‘E’ went.)

RAISERROR('The number of the bus is 2525.', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';

RAISERROR('It''s running downtown from Venice.', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';

Much better! By using RAISERROR (I said don’t ask!) and WITH NOWAIT, we get exactly that — no waiting.

PRINT cannot be used with WITH NOWAIT. Sorry.

RAISERROR has several arguments, but to replace PRINT, we just need to provide three:

• Message — the text to be returned
• Severity — a number between 0 and 25 that causes the error to be treated differently.
• State — a number between 0 and 255 that helps distinguish one error from another. Unless you have a need for this, just using 1 for everything is fine.

The neat thing about RAISERROR is that is doesn’t actually have to raise an error in the traditional sense. The severity of the error is variable and depending on the value, may appear differently in the Messages tab of SSMS.

Making the Error Gods Angry

So far, we’ve raised errors as harmless information. Let’s make one go bad, shall we?

Try this:

RAISERROR('The number of the bus is 2525.', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';

RAISERROR('It''s running downtown from Venice.', 16, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';

and you’ll get this:

The number of the bus is 2525.
Msg 50000, Level 16, State 1, Line 4
It's running downtown from Venice.

By changing the severity from 0 to 16, we’ve turned it into a more typical error message, one that will show up in the Event Log as an error rather than information or a warning. Note that even though the severity is 16, it didn’t halt query execution. Without getting too deep into error handling, we’ll just say use 0 for information and 16 for errors, with the expectation that neither one will stop your query from rolling on.

There’s more you can do with RAISERROR, like pass in variables to be printed in the error message, but the important takeaway here is that you don’t have to be disappointed by PRINT anymore.

Stop using PRINT. Start using RAISERROR.

I was much happier once I switched, and I’m sure you will be too.

(And no, I still don’t know where the other “E” went.)