SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements

The topic describes the SA0169 analysis rule.

Message

Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements

Description

The rule checks for @@ROWCOUNT being tested not directly after SELECT, INSERT, UPDATE, DELETE or MERGE statements.

Usage of the @@ROWCOUNT function after other kinds of statements will not return the expected count of affected rows.

After such other kinds of statements, the @@ROWCOUNT will either be constant (0 or 1) or return the preserved value of the previous statement.

How to fix

Review the usage of the @@ROWCOUNT and consider it as a potential bug. To resolve the issue, move the @@ROWCOUNT right after the SELECT, INSERT, UPDATE, DELETE or MERGE statements.

Scope

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

Parameters
Name Description Default Value
IgnoreRowcountInFirstSatementInsideTrigger

The parameter specifies whether or not to ignore the @@ROWCOUNT when found inside the first statement of a trigger.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE TABLE Test.Greeting
 2(
 3GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4Message nvarchar(255) NOT NULL,
 5)
 6 SELECT @@ROWCOUNT
 7begin 
 8     begin
 9    INSERT INTO Test.Greeting (Message) 
10    SELECT 'Hello!'
11    UNION ALL 
12    SELECT 'Hi!'
13    UNION ALL
14    SELECT 'Hello, world!'
15    end
16end
17SELECT @@ROWCOUNT
18INSERT INTO Test.Greeting (Message) 
19VALUES ('How do yo do?'),
20        ('Good morning!'),
21        ('Good night!')
22PRINT @@ROWCOUNT
23DELETE  Test.Greeting WHERE GreetingId = 3
24PRINT @@ROWCOUNT
25SELECT * FROM Test.Greeting g 
26WHERE 
27g.Message like 'Hello%'
28PRINT @@ROWCOUNT
29DROP TABLE Test.Greeting
30PRINT @@ROWCOUNT
31UPDATE HumanResources.Employee   
32SET JobTitle = N'Executive'  
33WHERE NationalIDNumber = 123456789  
34PRINT @@ROWCOUNT
35PRINT @@ROWCOUNT
36DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
37PRINT @@ROWCOUNT
38MERGE INTO Sales.SalesReason AS Target  
39USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), 
40              ('Internet', 'Promotion'))  
41       AS Source (NewName, NewReasonType)  
42ON Target.Name = Source.NewName  
43WHEN MATCHED THEN  
44UPDATE SET ReasonType = Source.NewReasonType  
45WHEN NOT MATCHED BY TARGET THEN  
46INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
47OUTPUT $action INTO @SummaryOfChanges;  
48PRINT @@ROWCOUNT
Analysis Results
  Message Line Column
1 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 6 8
2 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 17 7
3 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 30 6
4 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 35 6
5 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 37 6
See Also

Other Resources

Analysis Rules