Friday, November 22, 2013

Displaying a message from a T-SQL query in real time

The preferred method to display anything in Transact-SQL is PRINT. You can print a string, a variable, an expression. However, as anyone soon finds out, the message get all cached in a buffer and displayed after the entire query ends. So if you have several long running queries in a single batch and you want to get real time messages from them, PRINT doesn't work. A quick search directs you to another MS SQL directive: RAISERROR (note the creative spelling that makes one think more of hearing Katy Perry RROR rather than a proper error raising directive). Also note that Microsoft recommends using a new construct called THROW, introduced in SQL2012. However, it only looks like a lamer version of RAISERROR. They both send a message to the client instantly, but the problem they have is that they do not, as PRINT does, accept an expression. So if you want to print something like 'The computed key from the query is: '+@Key you are out of luck as you need to declare a new nvarchar variable, fill it with the message value then use it in RAISERROR.

But there is a better solution. RAISERROR not only throws something at the client, it also flushes the message cache. So something like this works: PRINT 'The computed key from the query is: '+@Key; RAISERROR('',0,1) WITH NOWAIT;.

Of course, being the dev that I am, I wanted to encapsulate this into something that would be reusable and also get rid of the need do use plus signs and conversion to NVARCHAR, so I created this procedure that almost works like PRINT should have:
CREATE PROCEDURE Write (@P1  NVARCHAR(max)=NULL, 
                        @P2  NVARCHAR(max)=NULL, 
                        @P3  NVARCHAR(max)=NULL, 
                        @P4  NVARCHAR(max)=NULL, 
                        @P5  NVARCHAR(max)=NULL, 
                        @P6  NVARCHAR(max)=NULL, 
                        @P7  NVARCHAR(max)=NULL, 
                        @P8  NVARCHAR(max)=NULL, 
                        @P9  NVARCHAR(max)=NULL, 
                        @P10 NVARCHAR(max)=NULL) 
AS 
    PRINT Isnull(@P1, '') + Isnull(@P2, '') 
          + Isnull(@P3, '') + Isnull(@P4, '') 
          + Isnull(@P5, '') + Isnull(@P6, '') 
          + Isnull(@P7, '') + Isnull(@P8, '') 
          + Isnull(@P9, '') + Isnull(@P10, '') 

    RAISERROR('',0,1) 

And you use it as DECLARE @now DATETIME = GetDate()
Write 'The date today is ',@now
. Nice, huh? Of course what you would have liked to do is Write 'The date today is ',GetDate(), but apparently stored procedures do not accept functions as parameters, and functions do not accept PRINT inside them.

1 comments:

Andrei Rinea said...

THROW is not lame. It simplifies error management in T-SQL. Really, have a look on some samples.