Click or drag to resize

SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers

The topic describes the SA0111 analysis rule.

Message

Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers

Description

The rule checks for WAITFOR statement with DELAY or TIME being used inside stored procedure, function or trigger.

The WAITFOR statement blocks the execution of the batch, stored procedure, or transaction until a specified time or time interval is reached. This is not tipically wanted in a OLTP system unless for a very specific reason.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0111

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
SQL
 1CREATE PROCEDURE testsp_SA0111
 2    (
 3    @DelayLength char(8)= '00:00:00'
 4    )
 5AS
 6BEGIN
 7    WAITFOR DELAY @DelayLength
 8
 9        WAITFOR DELAY @DelayLength -- IGNORE:SA0111
10
11        WAITFOR TIME '10:20:00 00:00:00:' 
12
13    DECLARE @conversation_group_id UNIQUEIDENTIFIER
14
15    WAITFOR (
16        GET CONVERSATION GROUP @conversation_group_id 
17        FROM ExpenseQueue ),
18    TIMEOUT 60000 ;
19
20END;

Analysis Results

 MessageLineColumn
1SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers.74
2SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers.111
See Also

Other Resources