SQL WAITFOR Command with WHILE Loop
I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour. I needed it to run at a specific time and to execute in 10 minute incriments and stop . I was hoping to log the results to a file, but this does just as well. When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.
This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count. You could just as easily use a counter and have it execute a certain number of times.
–don’t even start until 11pm
WAITFOR TIME ’23:00:00.000′
declare @endtime datetime
set @endtime = cast(’2009-05-09 00:00:00.000′ as datetime)
–stop at midnight
while getdate() < @endtime
begin
–every 10 minutes, execute
WAITFOR DELAY ’00:10:00′;
select getdate(), count(*)
from tablename
where datetimecolumn > ’5/7/2009′
and datetimecolumn < ’5/8/2009′
end
Kudos to Jerry for helping me with this.
I realise this was posted over a year ago, but wouldn’t it be better to just setup a scheduled task that runs between 23:00:00 and 00:00:00 daily that sends you an email, rather than having a job running for however many hours before it is actually required? That way you avoid using loops and the waitfor command.
Yeah, you’re absolutely right, that would be the “correct” way to do it. This was just a generic example, but I did end up having to use something like this because we weren’t allowed to create SQL Agent jobs
We had to process millions of rows in small chunks, so for what it’s worth, it does work well in a crunch.