See MSDN:
Http://msdn.microsoft.com/zh-cn/library/ms187331.aspx
The syntax is:
{
[, TIMEOUT TIMEOUT]
}
The following example executes a stored procedure at night 10:20 ( 22:20 ) sp_update_job .
Copy Code code as follows:
Use msdb;
EXECUTE sp_add_job @job_name = ' testjob ';
BEGIN
WAITFOR time ' 22:20 ';
EXECUTE sp_update_job @job_name = ' testjob ',
@new_name = ' updatedjob ';
End;
Go
The following example executes a stored procedure after a two-hour delay. Note: Delay is no more than 24 hours
Copy Code code as follows:
BEGIN
WAITFOR DELAY ' 02:00 ';
EXECUTE sp_helpdb;
End;
Go
The following example shows how to use local variables for the WAITFOR DELAY option. A stored procedure is created that waits for a variable period of time and then returns the elapsed hours, minutes, and seconds information to the user.
Copy Code code as follows:
Use ADVENTUREWORKS2008R2;
Go
IF object_id (' dbo. Timedelay_hh_mm_ss ', ' P ') is not NULL
DROP PROCEDURE dbo. TIMEDELAY_HH_MM_SS;
Go
CREATE PROCEDURE dbo. Timedelay_hh_mm_ss
(
@DelayLength char (8) = ' 00:00:00 '
)
As
DECLARE @ReturnInfo varchar (255)
IF ISDATE (' 2000-01-01 ' + @DelayLength + '. 000 ') = 0
BEGIN
SELECT @ReturnInfo = ' Invalid time ' + @DelayLength
+ ', HH:MM:SS, submitted. ';
--This PRINT statement was for testing and not with in production.
PRINT @ReturnInfo
Return (1)
End
BEGIN
WAITFOR DELAY @DelayLength
SELECT @ReturnInfo = ' A total of ' + @DelayLength + ',
HH:MM:SS, has elapsed! Your time are up. '
--This PRINT statement was for testing and not with in production.
PRINT @ReturnInfo;
End;
Go
/* This statement executes the dbo. TIMEDELAY_HH_MM_SS procedure. */
EXEC timedelay_hh_mm_ss ' 00:00:10 ';
Go
Execution Result: A total of 00:00:10, in Hh:mm:ss, has elapsed. Your time are up. Summary: This is a lightweight solution. When you do not have permission to specify a job, consider using the WAITFOR statement.
Invite the Month Note: This article copyright by invite month and the blog Garden Common All, reprint please indicate the source.
