WAITFOR what?

Published 22 November 10 01:12 PM | MartinBell

I raised a feedback to sqldocfb@microsoft.com regarding the documentation for the WAITFOR statement in BOL. It has been the same ever since SQL 2000 and I think it is wrong

WAITFOR
{
    DELAY 'time_to_pass
'
  | TIME 'time_to_execute
'
  | [ ( receive_statement ) | ( get_conversation_group_statement )
    [ , TIMEOUT timeout ]
}

But if it was consistent with other items in BOL such as RAISERROR it would be documented as

WAITFOR
{
    DELAY { time_to_pass | @local_variable }
  | TIME { time_to_execute | @local_variable }
  | [ ( receive_statement ) | ( get_conversation_group_statement )
    [ , TIMEOUT timeout ]
}

Because of this, I’m not sure how many people know about the ability to use variables when doing a WAITFOR statement so here are a couple of examples:

DECLARE @datetime datetime ;

SET @datetime = '00:00:10.000' ;

WAITFOR DELAY @datetime ;
GO

or

DECLARE
@datetime datetime ;

SET @datetime = DATEADD(ss,10,GETDATE())
;

WAITFOR TIME @datetime
;
GO

BOL talks about being able to specify an acceptable formats for a datetime datatype, then excludes having a specifying a date, which does make things clear. But this may also lead you to believe that only the datetime datatype can be used as a the local variable, when this isn't the case and the following will also works:

DECLARE @chartime char(12) ;

SET @chartime = '00:00:10.000' ;

WAITFOR DELAY @chartime ;
GO

What does seem a shame is that it doesn’t support the new time datatype. This will not work in SQL 2008

DECLARE @time time(3) ;

SET @time = '00:00:10.000' ;

WAITFOR DELAY @time ;
GO

and you get the error:

Msg 9815, Level 16, State 0, Line 5

Waitfor delay and waitfor time cannot be of type time.

This issue was raised as a connect by Steve Kass as the issue https://connect.microsoft.com/SQLServer/feedback/details/292843/katmai-issues-with-waitfor-delay-and-new-types# before SQL 2008 was released, but it hasn’t been subsequently fixed and on the Denali CTP1 is it still an issue. It certainly annoys me, so if you feel the same vote for it on connect.

Filed under: ,

Comments

No Comments

This Blog

SQL Blogs

Syndication