SQL Feather and Quill

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