Please visit our sponsor
<![CDATA[UNKNOWN
--**************************************
-- 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
--
--
-- Inputs:None
--
-- Returns:None
--
--Assumes:None
--
--Side Effects:None
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.205/lngWId.5/qx/vb/scripts/ShowCode.htm
--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
]]>