@@ROWCOUNT (Transact-SQL)

@@ROWCOUNT (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Topic link icon Transact-SQL Syntax Conventions

  
@@ROWCOUNT  

int

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

  • Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.

  • Preserve @@ROWCOUNT from the previous statement execution.

  • Reset @@ROWCOUNT to 0 but do not 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 may 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, 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 do not set @@ROWCOUNT. For more information, see Natively Compiled Stored Procedures.

The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee   
SET JobTitle = N'Executive'  
WHERE NationalIDNumber = 123456789  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were updated';  
GO  

System Functions (Transact-SQL)
SET ROWCOUNT (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft