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

Ensure proper usage of @@ROWCOUNT to accurately track affected rows.

Description

In T-SQL code, @@ROWCOUNT is a system function used to capture the number of rows affected by the last executed SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Using it incorrectly can lead to inaccurate results, especially when not immediately following one of these statements.

For example:

SQL
Copy
1-- Problematic use of @@ROWCOUNT
2SELECT * FROM Employees;
3-- Some other statements
4PRINT @@ROWCOUNT;

In this example, @@ROWCOUNT may not deliver the expected number of rows affected by the SELECT query, as its value might have changed or been preserved from unrelated operations.

  • Incorrect row count can lead to inaccurate program logic or misleading information displayed to users or used in further processing steps.

  • Preserved values could cause confusion and bugs if assumptions are made about the row count in subsequent logic.

How to fix

Ensure the accurate use of @@ROWCOUNT to capture the correct number of rows affected by SQL statements.

Follow these steps to address the issue:

  1. Immediately use @@ROWCOUNT after the SELECT, INSERT, UPDATE, DELETE, or MERGE statement to capture the precise number of affected rows.

  2. Avoid executing other statements between the SQL operation and the @@ROWCOUNT call, as it could alter the value or cause confusion.

  3. Consider storing the value of @@ROWCOUNT in a variable if you need to reference it later in your code.

For example:

SQL
Copy
1-- Correct use of @@ROWCOUNT
2SELECT * FROM Employees;
3PRINT @@ROWCOUNT;
4
5-- Storing @@ROWCOUNT in a variable
6UPDATE Employees SET Salary = Salary * 1.1;
7DECLARE @AffectedRows INT;
8SET @AffectedRows = @@ROWCOUNT;
9PRINT @AffectedRows;

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(
 3    GreetingId INT IDENTITY (1,1) PRIMARY KEY,
 4    Message nvarchar(255) NOT NULL,
 5)
 6PRINT @@ROWCOUNT
 7
 8INSERT INTO Test.Greeting (Message) 
 9VALUES ('How do yo do?'),
10        ('Good morning!'),
11        ('Good night!')
12PRINT @@ROWCOUNT
13
14DELETE  Test.Greeting WHERE GreetingId = 3
15PRINT @@ROWCOUNT
Analysis Results
  Message Line Column
1 SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. 6 6
See Also

Other Resources

Analysis Rules