WAITFOR Construct
From SQLServerPedia
|
See Also: Main_Page - Transact SQL Language Elements - Loops & Conditional Processing Sometimes your program might have to wait for a specified period of time or until a certain moment in time before it starts executing or before it launches a particular transaction. A good example is when you wish to pause the execution of a stored procedure to reduce locking contention on a certain table or when your program has to wait until a certain time when a nightly job completes. The WAITFOR construct would be used for this. The WAITFOR syntax is shown below: WAITFOR { DELAY 'time' | TIME 'time' } The WAITFOR DELAY option tells the program to hold until the specified period of time has elapsed. Maximum delay period is 24 hours. For instance, if you wish to stop the execution for 5 minutes you would write the following code: WAITFOR DELAY '00:05:00' You can also pass the amount of delay to the WAITFOR command as a variable, as in the following example: DECLARE @delay VARCHAR(12) SELECT @delay = '00:00:10' WAITFOR DELAY @delay SELECT * FROM authors Another option would be to base the program execution delay on the occurrence a particular event. For instance if your table needs to be populated by a BULK INSERT statement and you will have to wait until the table is fully populated before running any consistency checks. The WAITFOR statement would have to be combined with a WHILE loop, as follows: WHILE (SELECT COUNT(*) FROM authors) = 0 WAITFOR DELAY '00:05:00' WAITFOR TIME will wait until the specified time occurs within the next 24 hours. Only the time portion of a DATETIME can be specified with WAITFOR TIME, you are not allowed to specify the date. The following example will wait until 6pm (18:00) before continuing execution: WAITFOR time '18:00' SELECT * FROM authors Again, you have an option to pass the time as a variable instead of hard coding the value. Note: Any connection that uses the WAITFOR construct is effectively locked until the occurrence of a specified event or time. Do NOT use WAITFOR inside of a transaction because it will hold the locks on the tables queried by the particular program for the duration of the transaction. |