on October 12, 2010
Note: Updated 10/28 based on conversation in the comments, and 12/10 with Denali info.
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.
REGISTER