--**************************************
--     
-- Name: sample code that shows how to r
--     eturn progress info from long running SP
--     s
-- Description:Some sample code that sho
--     ws how to return progress info from long
--      running SPs. This is useful for reporti
--     ng purposes or to include other mechanis
--     ms to control the SP execution.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/xq/ASP/txtCod
--     eId.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 secon
    --     ds
    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 n
--     umber of steps
SELECT MAX( Step ) % 1000.0 AS StepPer
FROM ##Progress