SQL Server WAITFOR Delay 使用变量问题解决办法

作者:袖梨 2022-06-29

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;

相关文章

精彩推荐