Binaryworld - Sample for how to send progress info from long running sps. ... [ SQL Server -> T-SQL ]

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.

Click here to copy the following block

--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