Very lengthy stored procedure status

Question

I have a stored procedure which takes about 5-8 mins to execute. The user just sees a message "Please wait while report is being generated" It is very likely that people may think that it has stopped working ... or something went wrong.. etc..

Is there any way a stored procedure can keep returning status while executing..?

for example :

{logical block 1}
logical block 1 completed!
{logical block 2}
logical block 2 completed!
{logical block 3}
logical block 3 completed!

If yes, Please also tell me me how do I catch those status in c#.

Thanks guys.

Answer 1

You can use the InfoMessage event to receive PRINT and RAISERROR messages on the client.

If that doesn't work, I'm afraid you'd have to split your stored procedure up into multiple smaller procedures to get the status at each step.