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 topically wanted in a OLTP system unless for a very specific reason.

How to fix

Review the code and remove the WAITFOR DELAY/TIME statement.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers. 7 4
2 SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers. 11 1
See Also

Other Resources

Analysis Rules