@ @ROWCOUNT (Transact-SQL)

@ @ROWCOUNT (Transact-SQL)

Returns the number of rows affected by the previous statement. If the number of rows is greater than 2 billion, use Rowcount_big.

Transact-SQL Syntax conventions

Grammar

@ @ROWCOUNT
return type

Int

Note

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

Sets the @ @ROWCOUNT to the number of rows affected or read. Rows can be sent to the client or not sent.

Keep the @ @ROWCOUNT in the previous statement execution.

Resets the @ @ROWCOUNT to 0 but does not return the value to the client.

A statement that performs a simple assignment always sets the @ @ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are SET @local_variable, RETURN, READTEXT, and a SELECT statement without a query, such as Select GETDATE () or select ' Generic Text '.

The statement that performs the assignment in the query or uses RETURN will set the @ @ROWCOUNT value to the number of rows that are affected by the query or read by the query, for example: SELECT @local_variable = C1 from t1.

The Data Manipulation language (DML) statement sets the @ @ROWCOUNT value to the number of rows affected by the query and returns that value to the client. The DML statement does not send any rows to the client.

DECLARE CURSOR and FETCH set the @ @ROWCOUNT value to 1.

The EXECUTE statement retains the previous @ @ROWCOUNT.

Statements such as use, SET option, deallocate cursor, CLOSE cursor, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Example

The following example executes the UPDATE statement and uses the @ @ROWCOUNT to detect whether any of the rows have changed.

 UseAdventureWorks;GOUPDATEHumanResources.EmployeeSETTitle=N'Executive'WHERENationalidnumber= 123456789IF @ @ROWCOUNT = 0PRINT 'Warning No rows were updated';GO

Reprint: Prompt also play blog

@ @ROWCOUNT (Transact-SQL)

@ @ROWCOUNT (Transact-SQL)