TSQL Tuesday #11: Messages and the Clammy Seduction of the Print Statement

Note: Updated 10/28 based on conversation in the comments, and 12/10 with Denali info.

TSQL Tuesday #11: Misconceptions in SQL Server

This month’s #tsql2sDay is hosted by Sankar Reddy, and the topic is roughly, “Things you thought you knew, but didn’t.

For my entry, I’m picking a misconception that I periodically fall victim to myself. The misconception is basically, “It’s best to use the PRINT command for all informational messages, and RAISERROR is just for errors.”

The Ubiquitous Print Statement

We all learn the print command as one of our earliest commands with TSQL in SQL Server. It works like this:

print 'The enraged badger bit the angry stoat on its prickly posterior.'

The Poorly Named RAISERROR()

Most people also learn early on that print is not the preferred item for returning errors to calling applications. We learn to use RAISERROR for that.

BEGIN TRY
--cause an error forcing us to go to the catch block
exec sp_executesql N'select top 1 * from dbo.thisdoesnotexist'
END TRY
BEGIN CATCH
SELECT @errorMessage= 'Welcome to the catch block: ' + ERROR_MESSAGE()
RAISERROR ( @errormessage, 16, 1 )
END CATCH

I think RAISERROR() is pretty poorly named. I would rather it was named something along the lines of MESSAGEHANDLER() or HOWMAYIHELPYOUMASTER(). It’s really just a helper function for delivering messages, which may or may not be errors.

The Misconception: It’s Appropriate to Use Print for Informational Messages

Although many of us understand how to use RAISERROR, its name is pretty misleading. It says it’s to be used for ERRORS.

So we are still inclined to use PRINT for informational messages that we just want to display when running a script or procedure within SQL Server, and we’re also inclined to use it for debugging dynamic sql or anything with contatenated strings.

The Books Online page for ‘Using Print’ shows samples of this type of usage.

However, if you check out the Books Online Page for the Print statement itself, it warns you off a little in the ‘Remarks’ section:

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

This character limit can be a pain.

What isn’t mentioned in Books Online is that it can take quite a while for PRINT messages to be printed. If you’re running a script which takes some time, the message isn’t necessarily buffered to the client when something occurs: it can be minutes, or even an hour or more before the message is buffered out. This leaves the person running the script without any helpful log of what has occurred, and they need to rely on DMVs and other commands to determine what the script is doing at the present time.

The truth is that it just feels easy to use the print statement for returning messages, because we learned it first. And the syntax is dead simple. But it’s not like the more appropriate solution is a lot of work.

The Right Approach: Use RAISERROR for All Message Handling and Errors

The right thing to do is to use RAISERROR for all our message handling, as well as error handling. For informational messages where you don’t want to transfer control to the catch block, just use a severity of 0-10.

Main benefits:

  • Consistent approach to handling messages in your application (error and non error);
  • Messages are always given an explicit severity, to define whether or not they are an error, and how that can be acted on;
  • You have the option to use the NOWAIT option to stream output to the client. Note to developers: please use this when designing scripts for operations teams to run, which may not run quickly in production! Making sure the output is flushed promptly greatly simplifies checking the current status of a script.

Why You Can’t Do Away With PRINT: Debugging Long Dynamic SQL Strings

There’s one case where PRINT remains desirable: debugging long dynamic SQL strings.

Remember the note above regarding truncation of strings returned by PRINT? The same issue exists with RAISERROR, which returns only 2048 characters.

When building dynamic SQL strings, it’s often desirable to set a parameter so the string is printed instead of being run (or in addition to being run). For this, neither PRINT nor RAISERROR work well by themselves. In this case, you want to build a specialized procedure to print the entire message. A quick search shows quite a few of these have been posted for review and download.

Denali Update!

There’s a new kid coming to town: THROW. Aaron Bertrand gives a great comparison of THROW and RAISERROR Β here. Sadly, THROW does not have the NOWAIT option.

13 Comments. Leave new

  • I thought NoWait was for when you change your mind about raising an error. Like so:

    RaisError (‘Throw this error.’, 16, 1) With NoWait Don’tThrowthisError;

    8-P

    Reply
    • Robert, I think there’s special facilities for people who make geek jokes about RAISERROR. πŸ™‚

      But for some reason your comment did remind me of one thing about NOWAIT which can look odd– it flushes the message buffer, so any other messages in the buffer which are hanging out there will come through also.

      Please feel free to take this opportunity to make any jokes on flushing as needed. πŸ™‚

      Reply
      • RAISERROR will also cause resultsets to be flushed in SSMS. So if you have two SELECTs and one is quick and small and the other is slow, then the first will not start displaying unless the seconds output starts coming down. A RAISERROR between the SELECTs will get you the first SELECTs results.

        Reply
  • That’s spot-on.,.. I’ve been so much in the habit of using the print statement because RAISERROR()’s name has always led me to believe that it’s only for errors. Now I know “the Rest of the Story”. Changing print statements to RAISERROR()s πŸ™‚

    Reply
  • Erin Stellato
    October 12, 2010 6:53 pm

    I love this post. I am lazy and use print when debugging. Now I will think of this post every time I consider using it πŸ™‚ And I learned something new about NOWAIT and Robert’s taste for really geeky jokes!

    Erin

    Reply
  • Nice one! I love me some RAISERROR … But the real misconception is the pronunciation of the word.

    It’s not “raise error.” Rather, it’s “raise roar.”

    (At least, that’s how the members of the T-SQL programmability team refer to it :-))

    Reply
  • But… RaisError only displays 2044 characters. This is a more severe limitation than PRINT.

    Reply
    • Oh, look at that– it’s 2047. You get 2044 if you go over it because it swaps in an ellipsis.

      Thanks for the comment– this is why blogging is awesome.

      So I’m going to stick to the claim that raiserror is better for message handling– because of the specific typing of severity level and also because of the ability to flush the log. I’ve seen waaaay too many longrunning loops where the messages just take forever to flush out.

      But for the case of debugging long dynamic sql strings, methinks a helper function is going to be needed which moves through the string and outputs it in chunks. And because of the RAISERROR message headers, methinks print is actually going to be more useful for that.

      Clammy seduction, indeed.

      I’ll update the body of the post tomorrow after I do some testing.

      Reply
  • Nice post Kendra,

    I think that the real strength of raiserror is that it can format the string a bit ANSI” C like – quite useful in CATCH block.
    Also NOWAIT with all that flushing gives you much more insight into progress of long running batches.

    Reply
  • […] Kendra brings some light on the PRINT vs RAISERROR and explains why RAISERROR is the preferred way to send (debugging/troubleshooting) messages. […]

    Reply
  • […] Kendra Little on the “Clammy Seduction of the Print Statement” – I can’t stop giggling when I read that title.Β  I’ll go on record right now to say that Kendra is going to be one of the most popular speakers on the SQL Server circuit within the next two years.Β  Attendees will get baited in by her excellent abstracts, and she’s got the charisma to deliver a great presentation. […]

    Reply
  • […] Brent Ozar posted recently on his favourite blog posts one of which was a really good article by Kendra Little on reasons to use RAISERROR instead of PRINT […]

    Reply

Leave a Reply to The Twelve Days of SQL Series | Brent Ozar - Too Much Information Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu