Making RAISERROR work like PRINT

by data-centric on December 10, 2010

One of my favourite bloggers, 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 statements.

The synopsis of Kendra’s post was that whilst we all learn early on in our development careers to use PRINT statements to debug T-SQL stored procedures, RAISERROR is better for a number of reasons, especially used with NOWAIT.  Kendra suggests that using a severity level beyween 1 and 10 will enure that T-SQL’s TRY… CATCH… won’t pick such notifications up as errors and neither should any database API.

I agree with the benefits put forward but have always found the output to be hard to decipher in SSMS where I do much of my debugging, for example, the statement:
[SQL]RAISERROR(‘DEBUG: This is a test message’, 1, 1) WITH NOWAIT[/SQL]
…yields output like this:

DEBUG: This is a test message
Msg 50000, Level 1, State 1

As a one off, this is all good and well but on a big batch job you might end up with tens or even hundreds of these DEBUG statements and as it’s just debug information, I’m not interested in the line showing error number, level or state.

Then I accidentally discovered that if I pass a severity of 10, I don’t get the extraneous row, for example:
[SQL]RAISERROR(‘DEBUG: This is an easy-to-read test message’, 10, 1) WITH NOWAIT[/SQL]
…yields just this in the SSMS Messages tab:

DEBUG: This is an easy-to-read test message

I’ve tried this on two different instances of  SQL2008 and SQL2008 but can’t confirm whether this behaviour is the same on other versions.  Using a severity level of zero also hides the extra line but severity levels 1-9 all produce the unwanted line.

mbourgon February 16, 2012 at 9:14 pm

FWIW, RAISERROR(‘messagehere’, 0, 1) WITH NOWAIT will also work.

Greg M Lucas February 16, 2012 at 10:16 pm

Yes, thats true Michael, but for some reason Severity 1-9 doesn’t work the same way.

Thanks for your feed back

Comments on this entry are closed.

Previous post:

Next post: