Use RAISERROR over PRINT for returning messages quickly

Get messages while the batch is running.

Coming back to data warehousing in July 2009 has been a lot of fun for me. I haven’t done this stuff since I left Transamerica Life in 2005 I think it was, and realize that I enjoy working with facts, dimensions, etc. designing and building data marts and warehouses, and adding some value to the business. One thing that I haven’t missed is the long processing times; many routines or batches or processes, however you may refer to them, take hours or even days to complete. The quick turnaround in OLTP is definitely one thing I like about fast-paced transaction oriented database systems.

For long-running processes written in TSQL, I see a lot of folks adding PRINT statements to their work. Why they do this I have no idea – because PRINT is definitely not the way for really any type of TSQL coding that I can think of. I’d rather use RAISERROR. Even Books Online lists these reasons for using RAISERROR over PRINT:

RAISERROR can also be used to return messages. RAISERROR has these advantages over PRINT:

• RAISERROR supports substituting arguments into an error message string using a mechanism modeled on the printf function of the C language standard library.
• RAISERROR can specify a unique error number, a severity, and a state code in addition to the text message.
• RAISERROR can be used to return user-defined messages created using the sp_addmessage system stored procedure.

Unfortunately, they left out the most important reason – RAISERROR has the ability to ‘print’ the message while the batch is still running, without having to wait for the entire batch to complete. I looked over code from a 5-6 year SQL guy this week, and was surprised that he had all of these PRINT statements in his code. What if the process takes 36 hours to complete? You can’t know where a process is during the middle of it with PRINT. In this case you would need to have a logging process to see which procedure/script the process was executing. I always thought that this was Database 201, second-year stuff, maybe not.  Let’s review it to be sure that we have it down:

Here’s an example:

BEGIN
    WAITFOR DELAY '00:00:03.00'

    PRINT 'DELAY 1 HAS ENDED'

    WAITFOR DELAY '00:00:03.00'

    PRINT 'DELAY 2 HAS ENDED'
END

Note that you will not see the message until the entire script has completed. (If you need to go into text mode in SSMS by doing a CTL + T).  Here is the output:

DELAY 1 HAS ENDED
DELAY 2 HAS ENDED

Let’s run RAISERROR now. Note that I use the WITH NOWAIT option, so that it returns the result to the client…without waiting. You can see that, in the picture below, at 3 seconds after execution the first RAISERROR fires as the script keeps running, and at the end of the second raiserror the ‘DELAY 2’ message will run.

BEGIN
    WAITFOR DELAY '00:00:03.00'

    RAISERROR ('DELAY 1 HAS ENDED', 10,1) WITH NOWAIT

    WAITFOR DELAY '00:00:03.00'

    RAISERROR ('DELAY 2 HAS ENDED', 10,1) WITH NOWAIT

END

Keep this in mind the next time you start adding PRINT statements to your code. You’re probably better served to use RAISERROR. Maybe I’ll blog another one on the printf style messaging for RAISERROR, familiar if you had c or c++ in school. There are some neat things that you can do using TRY CATCH in this instance as well.

Thanks for reading,

Lee

------------------------

Tom Smykowski: Well-well look. I already told you: I deal with the * damn customers so the engineers don't have to. I have people skills; I am good at dealing with people. Can't you understand that? What the hell is wrong with you people?