@@ROWCOUNT (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
Transact-SQL syntax conventions
Syntax
@@ROWCOUNT
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return types
int
Remarks
Transact-SQL statements can set the value in @@ROWCOUNT
in the following ways:
- Set
@@ROWCOUNT
to the number of rows affected or read. Rows might or might not be sent to the client. - Preserve
@@ROWCOUNT
from the previous statement execution. - Reset
@@ROWCOUNT
to 0 but don't return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT
value to 1
. No rows are sent to the client. Examples of these statements are: SET @local_variable
, RETURN
, READTEXT
, and select without query statements such as SELECT GETDATE()
or SELECT '<Generic Text>'
.
Statements that make an assignment in a query or use RETURN
in a query set the @@ROWCOUNT
value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1
.
Data manipulation language (DML) statements set the @@ROWCOUNT
value to the number of rows affected by the query and return that value to the client. The DML statements might not send any rows to the client.
DECLARE CURSOR
and FETCH
set the @@ROWCOUNT
value to 1
.
EXECUTE
statements preserve the previous @@ROWCOUNT
.
Statements such as USE
, SET <option>
, DEALLOCATE CURSOR
, CLOSE CURSOR
, PRINT
, RAISERROR
, BEGIN TRANSACTION
, or COMMIT TRANSACTION
reset the ROWCOUNT
value to 0
.
Natively compiled stored procedures preserve the previous @@ROWCOUNT
. Transact-SQL statements inside natively compiled stored procedures don't set @@ROWCOUNT
. For more information, see Natively Compiled Stored Procedures.
Examples
The following example executes an UPDATE
statement and uses @@ROWCOUNT
to detect if any rows were changed.
USE AdventureWorks2022;
GO
UPDATE HumanResources.Employee
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO