--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 page was last updated on May 01, 2006 04:28 PM.