SQLServer中WaitFor延时执⾏的⽤法
在使⽤这个语句之前，我们先看看微软官⽅给的帮助⽂档⾥⾯对Waitfor的说明：
WAITFOR (Transact-SQL)
2017/03/15
本⽂内容
1.
2.
3.
4.
5.
适⽤于：SQL Server Azure SQL 数据库Azure Synapse Analytics (SQL DW) 并⾏数据仓库 APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
阻⽌执⾏批处理、存储过程或事务，直到已过指定时间或时间间隔，或者指定语句发⽣修改或⾄少返回⼀⾏为⽌。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.
语法Syntax
WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}
exists的用法
参数Arguments
DELAYDELAY
可以继续执⾏批处理、存储过程或事务之前必须经过的指定时段，最长可为 24 ⼩时。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''time_to_pass'
等待的时段。Is the period of time to wait. time_to_pass 可以以“datetime”数据格式指定，也可以指定为局部变量。time_to_pass can be specified either in a datetime data format, or as a local variable. 不能指定⽇期；因此，不允许指定“datetime”值的⽇期部分。Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_pass 将被格式化为hh:mm[[:ss].mss] 。time_to_pass is formatted as hh:mm[[:ss].mss].
TIMETIME
指定的运⾏批处理、存储过程或事务的时间。Is the specified time when the batch, stored procedure, or transaction runs.
'time_to_execute''time_to_execute'
WAITFOR 语句完成的时间。Is the time at which the WAITFOR statement finishes. 可以使⽤“datetime”数据格式指定 time_to_execute，也可以将其指定为局部变量。time_to_execute can be specified in a datetime data format, or it can be specified as a local variable. 不能指定⽇期；因此，不允许指定“datetime”值的⽇期部分。Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_execute 将被格式化为 hh:mm[[:ss].mss]，并且可以选择包括 1900-01-01 的⽇期。time_to_execute is formatted as hh:mm[[:ss].mss] and can optionally include the date of 1900-01-01.
receive_statementreceive_statement
有效的 RECEIVE 语句。Is a valid RECEIVE statement.
重要
包含 receive_statement 的 WAITFOR 仅适⽤于消息Service BrokerService Broker。WAITFOR with a receive_statement is applicable only to Service BrokerService Broker messages.有关详细信息，请参阅。For more information, see .
get_conversation_group_statementget_conversation_group_statement
有效的 GET CONVERSATION GROUP 语句。Is a valid GET CONVERSATION GROUP statement.
重要
包含 get_conversation_group_statement 的 WAITFOR 仅适⽤于消息Service BrokerService Broker。WAITFOR with a get_conversation_group_statement is applicable only to Service BrokerService Broker messages. 有关详细信息，请参阅。For more information, see .
TIMEOUT timeout TIMEOUT timeout
指定消息到达队列前等待的时间（以毫秒为单位）。Specifies the period of time, in milliseconds, to wait for a message to arrive on the queue.
指定包含 TIMEOUT 的 WAITFOR 仅适⽤于 Service BrokerService Broker 消息。Specifying WAITFOR with TIMEOUT is applicable only to Service BrokerService Broker messages. 有关详细信息，请参阅和。For more information, see and .
备注Remarks
执⾏ WAITFOR 语句时，事务正在运⾏，并且其他请求不能在同⼀事务下运⾏。While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.
实际的时间延迟可能与 time_to_pass、time_to_execute 或 timeout 中指定的时间不同，它依赖于服务器的活动级别。The actual time delay may vary from the time specified in
time_to_pass, time_to_execute, or timeout, and depends on the activity level of the server. 计划 WAITFOR 语句线程时，计时器开始计时。The time counter starts when the WAITFOR statement thread is scheduled. 如果服务器忙碌，则可能不会⽴即计划线程；因此，时间延迟可能⽐指定的时间要长。If the server is busy, the thread may not be immediately scheduled, so the time delay may be longer than the specified time.
WAITFOR 不更改查询的语义。WAITFOR doesn't change the semantics of a query. 如果查询不能返回任何⾏，WAITFOR 将⼀直等待，或等到满⾜ TIMEOUT 条件（如果已指定）。If a query can't return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified.
不能对 WAITFOR 语句打开游标。Cursors can't be opened on WAITFOR statements.
不能为 WAITFOR 语句定义视图。Views can't be defined on WAITFOR statements.
如果查询超出了 query wait 选项的值，则 WAITFOR 语句参数不运⾏即可完成。When the query exceeds the query wait option, the WAITFOR statement argument can complete without running. 有关详细信息，请参阅。For more information about the configuration option, see . 若要查看活动进程和正在等待的进程，请使⽤。To see the active and waiting processes, use .
每个 WAITFOR 语句都有与其关联的线程。Each WAITFOR statement has a thread associated with it. 如果对同⼀服务器指定了多个 WAITFOR 语句，可将等待这些语句运⾏的多个线程关联起来。If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL ServerSQL Server 将监视 WAITFOR 语句线程数，并在服务器开始遇到线程资源不⾜的问题时，随机选择其中部分线程退出。monitors the number of WAITFOR statement threads, and randomly selects some of these threads to exit if the server starts to experience thread starvation.
如果某个事务锁定了 WAITFOR 语句试图访问的⾏集以防⽌对⾏集进⾏更改，则可以在该事务中运⾏包含 WAITFOR 语句的查询来创建死锁。You can create a deadlock by running a query with WAITFOR within a transaction that also holds locks preventing changes to the rowset accessed by the WAITFOR statement. 如果存在上述死锁，则 SQL ServerSQL Server 会标识这些情况并返回空结果集。SQL ServerSQL Server identifies these scenarios and returns an empty result set if the chanc
e of such a deadlock exists.
注意
包含 WAITFOR 将减慢 SQL ServerSQL Server 过程的完成速度，并会导致应⽤程序中的超时消息。Including WAITFOR will slow the completion of the SQL ServerSQL Server process and can result in a timeout message in the application. 如有必要，请在应⽤程序级别调整连接的超时设置。If necessary, adjust the timeout setting for the connection at the application level.
⽰例Examples
A.A. 使⽤ WAITFOR TIMEUsing WAITFOR TIME
下⾯的⽰例在晚上 10:20 在 msdb 数据库中执⾏ sp_update_job 存储过程。The following example executes the stored procedure sp_update_job in the msdb database at 10:20 P.M. (22:20)。(22:20).
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
WAITFOR TIME '22:20';
EXECUTE sp_update_job @job_name = 'TestJob',
@new_name = 'UpdatedJob';
END;
GO
B.B. 使⽤ WAITFOR DELAYUsing WAITFOR DELAY
以下⽰例在两⼩时的延迟后执⾏存储过程。The following example executes the stored procedure after a two-hour delay.
BEGIN
WAITFOR DELAY '02:00';
EXECUTE sp_helpdb;
END;
GO
C.C. 在 WAITFOR DELAY 中使⽤局部变量Using WAITFOR DELAY with a local variable
以下⽰例显⽰如何对 WAITFOR DELAY 选项使⽤局部变量。The following example shows how a local variable can be used with the WAITFOR DELAY option. 该存储过程将等待可变的时间段，然后将经过的⼩时、分钟和秒数信息返回给⽤户。This stored procedure waits for a variable period of time and then returns information to the user as the elapsed numbers of hours, minutes, and seconds.
IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL
DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;
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 is for testing, not use in production.
PRINT @ReturnInfo
RETURN(1)
END
BEGIN
WAITFOR DELAY @DelayLength
SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ',
hh:mm:ss, has elapsed! Your time is up.'
-- This PRINT statement is for testing, not use 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
下⾯是结果集：Here is the result set.
A total time of 00:00:10, in hh:mm:ss, has elapsed. Your time is up.
另请参阅See Also
==============================================================================================
从上⾯的说明⾥，我们可以看到  TIMEOUT timeout 是可以精确到毫秒级的。
Datetime 格式的可以精确到毫秒，也就是10的负3次⽅秒，如对应获取系统时间的⽅法：getdate()
Datetime2 格式的可以精确到微秒，也就是10的负6次⽅秒，如对应获取系统时间的⽅法：sysdatetime()
正好，项⽬上有⽤到这块的内容，就简单的看看，研究下⽤法，我们先来测试看看。
延迟1毫秒
SELECT getdate()
WAITFOR DELAY  '00:00:00.001'
SELECT getdate()
两次的执⾏结果分别如下
2008-01-10 22:54:13.513
2008-01-10 22:54:13.513
两次获得的时间完全⼀样。
延迟两毫秒就能看返回的时间差别
SELECT getdate()
WAITFOR DELAY  '00:00:00.002'
SELECT getdate()
延迟两次的执⾏结果
2008-01-10 22:58:37.450
2008-01-10 22:58:37.467
由于我⽤的⼀个存储过程中需要毫秒级的时间串产⽣序列号，因为没有延迟，所以会出现序列号相同的问题，所以这个延迟函数就帮了⼤忙。SELECT convert(varchar(8),  getdate(),112)  +  replace(convert(varchar(12),getdate(),114),':','') as  NewSerial
WAITFOR DELAY  '00:00:00.002'
SELECT convert(varchar(8),  getdate(),112)  +  replace(convert(varchar(12),getdate(),114),':','') as  NewSerial 执⾏结果如下
20080110230256640
20080110230256653

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。