Using RAISERROR for Progress Messages

As mentioned in my last post, it is possible to use RAISERROR WITH NOWAIT in order to immediately send a message back to the client.  This is useful for long, procedural (i.e., not set-based) stored procedures that loop over many different rows.

Consider the following stored procedure:

CREATE PROCEDURE dbo.InfoMsgTest
AS
    DECLARE @i int;
    SET @i = 1;
    WHILE @i < 100
    BEGIN
        RAISERROR('%d', 0, 1, @i) WITH NOWAIT;
        -- Do some processing!
        WAITFOR DELAY '00:00:01';
        SET @i = @i + 1;
    END
GO

This procedure is a simple loop that counts to 100.  Each time around the loop, a RAISERROR command is executed, passing out the value of @i.  Any message at all could be passed – you could include how many rows have been processed, how many to go, and what the primary key is of the current row.

On the client, consider the following C# console application.  All error handling has been removed, and I haven’t written any .NET code in two years, so your forgiveness is appreciated!

using System;
using System.Data.SqlClient;

namespace InfoMessages
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection(
                "Data Source=(local);Initial Catalog=AdventureWorks;" 
                    + "Integrated Security=SSPI;");
            conn.InfoMessage += 
                new SqlInfoMessageEventHandler(InfoMessage);
            conn.Open();
            SqlCommand cmd = new SqlCommand("exec dbo.InfoMsgTest", conn);
            cmd.CommandTimeout = 120;
            Console.WriteLine("Processing starting.");
            cmd.ExecuteReader();
            conn.Close();
            Console.WriteLine("Processing complete.");
        }

        private static void InfoMessage (object sender, 
            SqlInfoMessageEventArgs e)
        {
            Console.WriteLine("Percent completed: " + e.Message + "%");
        }
    }
}

Note that it is vital to use a cmd.ExecuteReader().  cmd.ExecuteNonQuery() will not fire the InfoMessage handler. 

And the output:

There you have it!  A GUI application shouldn’t be too much harder.  Little things like this can make the difference between having a responsive application that informs the user as to what is happening, versus a black box that appears to hang for 30 seconds while the stored procedure is executed.