I am going to continue my articles (tidbits) on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.
The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.
Today, I want to talk about waiting a time period before executing a statement.
The WAITFOR statement was introduced in SQL Server 2005.
In it’s simplest form, it takes a time value as a parameter. The DELAY clause blocks the current batch process until the time period has elapsed. The TIME clause blocks the current batch process until our system clock reads that time.
The example below counts from one to twenty-five. I pauses one second every fifth number. This statement can pause any time period ranging from hours to seconds.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- -- Wait 1 sec for every # divisible by 5 -- -- Loop counter DECLARE @VAR_CNT INT = 1; -- Count to 25 WHILE (@VAR_CNT < 26) BEGIN -- Show the number PRINT @VAR_CNT; -- Pause when modulo is zero IF (@VAR_CNT % 5) = 0 BEGIN PRINT '...'; WAITFOR DELAY '00:00:01'; END -- Increment the counter SET @VAR_CNT += 1; END; GO |
The output of this program is shown below.
Please note, statement blocks can be nested like the IF statement above. The WAITFOR statement is at level 2 scope.
To wrap up this talk, the WAITFOR statement can be used to place delays into your T-SQL programs or execute a T-SQL program at a certain time.
Next time, I will use the IF/ELSE statement with the BREAK/CONTINUE commands to take control of an infinite loop.
