Getting feedback / progress from batches and stored procedures

Many a times, application development comes up with a requirement where if a stored procedure takes a certain period of time (say for ex. more than 30 secs) then the procedure should get terminated. One of the better option can be updating user with the status of what operation is being performed. This means updating the progress status of a stored procedure.

RAISERROR is the key to this solution. But before proceeding, there are certain facts which once should be aware of about RAISERROR:

• RaisError used with the wrong severity (second parameter) might terminate the batch or the connection, severities less or equal to 10 will just send an informational message to the calling stack.
• Syntax: RAISERROR (Message, Severity, State) –> e.g. RAISERROR (‘SomeMessageForYou’,0,10)
• Although the Information messages are a good way to send life signals back to the client, SQL Server will batch these informational messages also together up to the end of the batch unless you use the WITH NOTWAIT option of the RaisError.

Basically at the client, the following code can be used:

conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);

In the conn_InfoMessage method we are evaluating the information from the thrown “errors”, determine the calling batch from the Message text of the error and the progress from the state.

if (Message == "Batch1")
{ progressBar1.Value = PercentageComplete; }

else if (Message == "Batch2")
{ progressBar2.Value = PercentageComplete; }

Reference: While wandering the blogosphere, I came across an interesting blog post from a MCS consultant named Jens. This post is my version of explanation from the original post.