Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
One of the rarely used commands for me is the WAITFOR command. This is a command that intentionally introduces a delay in the execution of your code. I sometimes use this when I need to pause code for a brief time, but I never remember how this structure works.
Hopefully this quick post helps me remember this in the future.
WAITFOR
This command does what it says; it waits for something. You have two choices here in what to wait for: a period of time or a specific time. The structure of the command is:
waitfor <type> <time>
The type can be either of the keywords TIME or DELAY. I often use DELAY, which is a period of time to wait for. When you use TIME, the execution stops until that specific time of day.
As an example, if I want to pause code for 5 seconds, I use this:
WAITFOR DELAY ‘00:00:05’
If I run this in SSMS or ADS, the query time for this will be 5 seconds. The default is ‘hh:mm’, so remember that if you want seconds, you need to include the hours and minutes.
For time, the parameter is a datetime format, so enter this as the time you would want to start code execution again.
Practical Usage
The main place I used this recently was in this code:
EXEC msdb.dbo.sp_start_job @job_name = ‘Second Job with Two Errors’, @step_name=’Fourth Step’
WAITFOR DELAY ’00:00:02'
I was testing some job tracking, and needed a job that failed. It usually runs in less than a second, but without the delay, sometimes the failure isn’t picked up from the job history table.
This is the type of place, often in testing or in some dependent process, where I want a delay.
SQLNewBlogger
This post too my about 10 minutes to write. I couldn’t remember how WAITFOR works, but SQL Prompt helped. As I worked through my testing, I stopped and took 10 minutes to write this up.
You can do the same thing. Show your example, and how you use it. Be creative and impress someone who might read your blog before they interview you.