Question Create Progess Bar for SQL stored procedure

[ETERNAL NEWBIE]

Could anyone show me how to create a progess bar which discribes the process of a SQL stored procedure?
(Really needing help, I had stuck here for nearly a month. Appreciate any help from you guys!)​

 

[jmcilhinney]

You can't report progress that you can't measure. Can you measure the execution of a stored procedure? No you can't, so a ProgressBar is basically useless. You could use a data reader to read the result set one record at a time but you won't know how many records there are in total so, again, a ProgressBar is basically useless. All you can really do is set the Style to Marquee to indicate that something is happening without specifically indicating where in the process it's up to.

 

[ETERNAL NEWBIE]

Ok, I did that already, only trying to improve my project a lil bit, thk you.

 

[jmcilhinney]

When it comes to progress it's easy: if you can measure it then you can report it and if you can't then you can't. The ProgressBar really has nothing to do with it. It's just a fancy way to display a fraction to the user. It's the calculation of that fraction that matters.

 

[Herman]

Well you CAN report the progress of asynchronous queries by querying the SYS database on the instance. For example:

SELECT percent_complete, command, database_id
FROM sys.dm_exec_requests
WHERE percent_complete > 0 
  AND command LIKE '%BACKUP%'
  AND database_id = DB_ID('AdventureWorks')

Will tell you the percentage done on a running backup query on database AdventureWorks. So it all depends what your stored procedure does. If you have means of calculating percentage done from inside your stored procedure, you could take example on what SQL Server does natively and update a row in some table periodically to indicate the percentage done, and read that value from your client application.

Nothing is impossible...

 

[eomnidev]

What if my stored procedure has 3 steps (for example). I would like to see a progress bar that tell me which step is the current one.
Maybe there is a way to send a message, raise an event or something to tell which step is reached ?
Thanks for answers.

 

[Herman]

You could just assume the three steps each count for 33.3% of the total progress bar. Divide percent_complete by 3, and add each one to the previous.