Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row. This is very useful. Every day when I restore the database to backup server for reports post processing, I use WAITFOR clause.
While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time. WAITFOR can be used with query but not with UDF or cursors. WAITFOR wait till TIMEOUT is reached.
Examples of WAITFOR
----Delay for 20 seconds
WAITFOR DELAY '000:00:20'
SELECT '20 Second Delay'
GO
----Delay till 7 AM
WAITFOR TIME '7:00:00'
SELECT 'Good Morning'
GO
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
13 Comments. Leave new
verry interesting,
but as I know that this ‘waitfor delay’ will only block for the current session, so the sql server will process other requests for other sessions even if the delay interval doesn’t finish yet.
can you verify that, please ?
Thanks,
Mosab
First, I want to thank you for your blog, I inadvertently read it more than I think I would, it has been a great help.
Question for you though; I have a cursor which performs a number of actions on databases (CBCC CheckDB & Shrinkdatabase) as well as backs them up and zips them.
How would I best use a waitfor command (SQL 2000) to make sure the steps aren’t overlapping or leapfrogging the other? A time based variable may just cause more problems as far as I can see (may be too long or too short), so is this possible or am I barking up the wrong tree?
Thanks
Tim M
Thanks for the code
Thanks to give this solution. Thankyou so much!!
Thanks for the enlightenment, it’s fix my problem…
Wow , it’s work Man !!!
thanks a lot
Hi,
I read your blogs and find very usefull in solving the problrms releated to sql queries. My problem is that I have a stored procedure and when I execute it first time it take a lot of time vene up to 5-10 minutes.But second time it execute within 1 minutes. But if I execute some other query after its excution or execute the procedures after an interval it again take a long time to execute. Can u please suggest what I have to do to stop this long interval during execution?
Hi,
I am kind-of new to SQL server and having trouble with a stored procedure with a query timeout set to 9secs.
when i use the following sp,
CREATE PROCEDURE Sleepy @sleep INT AS
BEGIN
DECLARE @now DATETIME;
DECLARE @end DATETIME;
SET @now = SYSDATETIME();
SET @end = DATEADD(S, @sleep, @now);
WHILE (@now < @end)
SET @now = SYSDATETIME();
END;
i do not see a timeout , even tho' the sleepy is passed 10 secs.the sp completed execution wwithout interruption
However when i use "WAITFOR DELAY" in sp….
in 9 secs i get a timeout.
It is not clear , why in the first case, i am not receiving a timeout whereas the second case throws a query timeout exception as expected.
Please clarify
Thanks a lot your posts helps me a lot…
Please write a book. You are just awesome.
Thank you i go through it your blog and its very helpful to block processes(Stored Procedures) for a certain timer
I was able to use this within a cursor. I am on 2012 R2 version. In my case i am looping for all US states in cursor and then updating matching records for geolocation. Is there any drawback here. I wanted the sql server time to handle other requests as updating this table with 60 million records takes a toll on server.
Thanks for your explanations. I do need some help around an issue. I have a job that is required to start only when it is midnight. I need to generate a script to wait for delay until it is midnight before the job will kick off. if the step above the job completes before midnight, it needs to wait until it gets to midnight before it kicks off. Again, if the step above it completes a little after midnight, it needs to validate that it is already midnight and the job can kick start without waiting for delay for another midnight. Would really appreciate a help around creating this code.
Thanks