sql server2008帮助中, 有一段WAITFOR Delay 的示例, 是错误的!!!
USE AdventureWorks;
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 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
按照以上代码运行,会报错
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
(Note that the error is on the WAITFOR line.)
用time变量类型也不行!!!
declare @t time;
set @t = '00:00:01';
waitfor delay @t;
Actual Results
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
(Note that the error is on the WAITFOR line.)
如果直接用常量,没有问题。通过!!
WAITFOR DELAY ‘00:00:10’
如果非要用变量,只能这样写,要定义为datatime类型,然后随便用一个日期,在后跟你需要延迟的时间:
declare @t datetime;
set @t = '2007-01-01 00:00:60'; --延迟60秒
waitfor delay @t;
上面代码中的 2007-01-01 没有任何实际意义,可以用任何日期。但是日期是必须的。
如果你写成如下,
declare @t datetime;
set @t = '00:00:60'; --延迟60秒
waitfor delay @t;
编译和运行不会报错,但他不会延迟所规定的时间(60秒),也就是无延迟马上执行下一句。.原因不详,也许是SQL server的bug。
---------------------------------------
Steps to Reproduce
declare @t time;
waitfor delay @t;
-- Or:
declare @t time;
set @t = '00:00:01';
waitfor delay @t;
Actual Results
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
(Note that the error is on the WAITFOR line.)
Expected Results
At least a better error. The user has not supplied a character string, so the error should not mention character strings.
Ideally, these should work just like they do with datetime. The [time] type is a natural choice for WAITFOR DELAY.
The documentation doesn't help too much. While it suggests using [datetime] instead of [time], it says confusingly that "Dates cannot be specified; therefore, the date part of the datetime value is not allowed." However, this works:
declare @t datetime;
set @t = '2007-01-01';
waitfor delay @t;