Click or drag to resize

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.

Consider reviewing such usages as potential bugs.

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.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
 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

 MessageLineColumn
1SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.68
2SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.177
3SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.306
4SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.356
5SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.376
See Also

Other Resources