WAITFOR what ?

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

Read more: Martin Bell UK SQL Server MVP

Posted via email from .NET Info