Pass status information from Stored Procedure to caller inside transaction

I have a long-running SP (it can run for up to several minutes) that basically performs a number of cleanup operations on various tables within a transaction. I'm trying to determine the best way to somehow pass human-readable status information back to the caller on what step of the process the 开发者_StackOverflowSP is currently performing.

Because the entire SP runs inside a single transaction, I can't write this information back to a status table and then read it from another thread unless I use NOLOCK to read it, which I consider a last resort since:

• NOLOCK can cause other data inconsistency issues; and
• this places the onus on anyone wanting to read the status table that they need to use NOLOCK because the table or row(s) could be locked for quite a while.

Is there any way to issue a single command (or EXEC a second SP) within a transaction and tell specify that that particular command shouldn't be part of the transaction? Or is there some other way for ADO.NET to gain insight into this long-running SP to see what it is currently doing?

You can PRINT messages in T-SQL and get them delivered to your SqlConnection in ADO.NET via the "InfoMessage" event. See

http://msdn.microsoft.com/en-us/library/a0hee08w.aspx

for details.

You could try using RAISERROR (use a severity of 10 or lower) within the procedure to return informational messages.

Example:

RAISERROR(N'Step 5 completed.', 10, 1) WITH NOWAIT;