看到一篇文章,希望能对你有所用。
--**************************************
--
-- 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/vb/scripts/Sh
-- owCode.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 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