I was looking at some code I wrote the other day and noticed the WAITFOR command.. This got me thinking. How many times have I used WAITFOR in code, probably as much as I have used NTILE :-)
I looked at the documentation for WAITFOR and notice there is TIME in addition to DELAY. Oh that is handy, I always rolled my own ghetto-style version by calculating how long it would be until a specific time and then I would use that in the WAITFOR DELAY command
Why would you use the WAITFOR command?
The WAITFOR command can be used to delay the execution of command for a specific duration or until a specific time occurs. From Books On Line, the description is as follows:
Blocks the execution of a batch, stored procedure, or transaction until either a specified time or time interval elapses, or a specified statement modifies or returns at least one row.
WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}
Arguments
DELAY
Is the specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds.
'time_to_pass'
Is the period of time to wait. time_to_pass can be specified either in a datetime data format, or as a local variable. Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_pass is formatted as hh:mm[[:ss].mss].
TIME
Is the specified time when the batch, stored procedure, or transaction runs.
'time_to_execute'
Is the time at which the WAITFOR statement finishes. time_to_execute can be specified in a datetime data format, or it can be specified as a local variable. Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_execute is formatted as hh:mm[[:ss].mss] and can optionally include the date of 1900-01-01.
WAITFOR with a receive_statement or get_conversation_group_statement is applicable only to Service Broker messages. I will not cover those in this post
I must admit that I only use these commands a couple of times a year when running something ad-hoc. In code, I will use WAITFOR DELAY when doing a back fill of data, and the table is replicated. In that case I will batch the data and after each batch is completed I will pause for a second or so. The reason I am doing this is because I don't want to increase replication latency, after all, I am a nice guy
WAITFOR TIME
Let's take a look how you would use the WAITFOR command. I will start with WAITFOR TIME
The command is very easy.. if you want the print command to run at 09:57:16, you would do the following
WAITFOR TIME '09:57:16' PRINT 'DONE '
The seconds are optional, if you want it to run at 9 hours and 57 minutes, you can do the following
WAITFOR TIME '09:57' PRINT 'DONE '
One thing to know is that you can't grab the output from a time data type and use that in your WAITFOR TIME command. The following will blow up
SELECT CONVERT(time, getdate()) --'09:57:16.9600000' WAITFOR TIME '09:57:16.9600000'
Msg 148, Level 15 , State 1, Line 32
Incorrect time syntax in time string '09:57:16.9600000' used with WAITFOR.
What you need to do is strip everything after the dot.
We need the command to be the following
WAITFOR TIME '09:57:16'
There are two ways to accomplish this... first way is by using PARSENAME, I blogged about that function several times, the first time here: Ten SQL Server Functions That You Hardly Use But Should
All you have to tell SQL Server which part you want, if you use PARSENAME,1 you will get everything after the dot, if you use PARSENAME,2 you will get everything before the dot.
1 2 | SELECT PARSENAME('09:57:16.9600000',2), PARSENAME('09:57:16.9600000',1) |
This returns the following
09:57:16 9600000
The easiest way would have been to just use time(0) instead
1 2 | SELECT CONVERT(time, getdate()) ,--'09:57:16.9600000' CONVERT(time(0), getdate()) --'09:57:16 |
Below is a complete example that will wait for 10 seconds to run the PRINT statement on line 12 if you run the whole code block in 1 shot.
Also notice that I use a variable with the WAITFOR TIME command on line 9. The caveat with that is that the variable can't be a time datatype. This is why I use a varchar datatype and store the value of the time data type in it. The reason I use the time datatype in my procs is so that I don't have to do a lot of validations when someone is calling the proc. If they pass in a string that can't be converted.. the proc won't even run... it will fail right at the proc call itself
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @DelayTime time(0)= GETDATE() PRINT @DelayTime SELECT @DelayTime =DATEADD(second,10,@DelayTime) PRINT @DelayTime DECLARE @d varchar(100) = @DelayTime -- you have to use varchar in command WAITFOR TIME @d -- Run your command here PRINT 'DONE ' + CONVERT(varchar(100),CONVERT(time(0), getdate())) |
What is printed is the following
10:49:48
10:49:58
DONE 10:49:58
Now when would you really use WAITFOR TIME? You can accomplish the same with a scheduled job, the only time I use WAITFOR TIME is if I want a quick count of want to run something at a specific time but I know I won't be at my desk and I can't create a job without a ticket
But you also have to be aware that if your connection gets lost to the SQL Server instance, your command won't execute
WAITFOR DELAY
The WAITFOR DELAY command is similar to the WAITFOR TIME command, instead of waiting for a time, the command pauses for a specific period
Like I said before, I use WAITFOR DELAY as well as a batch size in my back fill procs. Both can be passed in, if you do a load during a weekday, your delay would be longer than on a weekend.
Sometimes I need to see how many rows are getting inserted every minute.. or something similar
I will then combine WAITFOR DELAY and the batch terminator with a count number to execute the batch of statements more than once
Here is such an example, it will run the INSERT statement 20 times, it will pause 1 minute between each execution
1 2 3 4 | INSERT #temp(SomeCol, SomeTimeStamp) SELECT COUNT(*), GETDATE() FROM sometable WAITFOR DELAY '00:01:00' GO 20 |
That's all for this post.
Do you use the WAITFOR command, if so, what do you use it for?
No comments:
Post a Comment