Every once in a while, you need to loop through a bunch of records in TSQL. It’s not the fastest way to do things, but sometimes it’s necessary. I’ve been working on a process that requires this and I needed to know the progress of the loop, so I came up with this model. The output will give you a bunch of statistics including a predicted finishing time every 1000 rows (note you can adjust this). This should save me from getting some grey hairs when a looping process is necessary.
Declare @counter int, @currentRow int, @rowsFound int, @start datetime, @SecSoFar bigint, @PredictedTotalSec int, @PercentComplete varchar(10), @CompleteTime datetime Set @rowsFound = 200000 -- or set this equal to the count of a table you are working with. Set @currentRow = 1 Set @start = GETDATE() while @currentRow <= @rowsFound Begin --<<DO YOUR WORK HERE>>-- Set @currentRow = @currentRow + 1 ---Update the counter.... If (@currentRow % 1000) = 0 --display every 1000 rows or adjust... Begin Set @SecSoFar = Datediff(s, @start, Getdate()) Set @PercentComplete = Cast(ROUND(@currentRow/Cast(@rowsFound as float), 3)*100 as varchar(20)) + '%' Set @PredictedTotalSec = (@SecSoFar/Cast(@currentRow as float))*@rowsFound Set @CompleteTime = Dateadd(s, @PredictedTotalSec, @start) Select @currentRow as 'Current_Row', @rowsFound as 'Total_Rows', @start as 'Start_Time', GETDATE() as 'Current_Time', @SecSoFar as 'SecondsSoFar', @PercentComplete as 'Percent_Complete', @PredictedTotalSec as 'Predicted_Total_Seconds', @CompleteTime as 'Predicted_Complete_Time' End End