Some sample code that shows how to return progress info from long running SPs. This is useful for reporting purposes or to include other mechanisms to control the SP execution.
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
--**************************************
-- Name: sample code that shows how to return progress info from long running SPs
-- Description:Some sample code that shows how to return progress info from long running SPs. This is useful for reporting purposes or to include other mechanisms to control the SP execution.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=205&lngWId=5--for details.--**************************************
--To store the progress details
/*
You can make this as complex as you want by adding more progress details,
mechanism to pause the job etc.
*/
CREATE TABLE ##Progress( Step int , ItTookSoLong int )
GO
CREATE PROC EverRunningProc
AS
DECLARE @step int , @Delay char(10)
SELECT @step = 1
WHILE( 1 = 1)
BEGIN
IF @step = 1000 BREAK
INSERT ##Progress VALUES( @Step , 0 )
-- Do random delay between 1 to 45 seconds
SELECT @Delay = CONVERT( varchar, DATEADD( ss, CEILING( RAND()* 44 ),
'1970-01-01') , 8 )
WAITFOR DELAY @Delay
UPDATE ##Progress
SET ItTookSoLong = datepart( ss, convert( datetime , @Delay ) ) ,
@Step = Step + 1
WHERE Step = @Step
END
GO
-- In your app, you can do:
-- Calculate percentage based on total number of steps
SELECT MAX( Step ) % 1000.0 AS StepPer
FROM ##Progress
This is neat! But it assumes that no other user can run this code in the same time because it uses ##, a global temp table. So it wouldn't be too useful unless different SP uses a different temp table name to track progress. (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 2 Users
10/19/2000 5:20:37 PM: 
