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

Introduction

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:

-- Problematic use of @@ROWCOUNT
SELECT * FROM Employees;
-- Some other statements
PRINT @@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:

-- Correct use of @@ROWCOUNT
SELECT * FROM Employees;
PRINT @@ROWCOUNT;

-- Storing @@ROWCOUNT in a variable
UPDATE Employees SET Salary = Salary * 1.1;
DECLARE @AffectedRows INT;
SET @AffectedRows = @@ROWCOUNT;
PRINT @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.

Additional Information

@@ROWCOUNT (Transact-SQL)

Example Test SQL

CREATE TABLE Test.Greeting
(
    GreetingId INT IDENTITY (1,1) PRIMARY KEY,
    Message nvarchar(255) NOT NULL,
)
PRINT @@ROWCOUNT

INSERT INTO Test.Greeting (Message)
VALUES ('How do yo do?'),
        ('Good morning!'),
        ('Good night!')
PRINT @@ROWCOUNT

DELETE  Test.Greeting WHERE GreetingId = 3
PRINT @@ROWCOUNT

Analysis Results

	Message	Line	Column
1	SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements.	6	6

Analysis Rules