@@ROWCOUNT (Transact-SQL)@@ROWCOUNT (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回受上一语句影响的行数。Returns the number of rows affected by the last statement. 如果行数大于 20 亿,请使用 ROWCOUNT_BIGIf the number of rows is more than 2 billion, use ROWCOUNT_BIG.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

@@ROWCOUNT  

返回类型Return Types

intint

RemarksRemarks

Transact-SQLTransact-SQL 语句可以通过下列方式设置 @@ROWCOUNT 的值:statements can set the value in @@ROWCOUNT in the following ways:

  • 将 @@ROWCOUNT 设置为受影响或被读取的行的数目。Set @@ROWCOUNT to the number of rows affected or read. 可以将行发送到客户端,也可以不发送。Rows may or may not be sent to the client.

  • 保留前一个语句执行中的 @@ROWCOUNT。Preserve @@ROWCOUNT from the previous statement execution.

  • 将 @@ROWCOUNT 重置为 0 但不将该值返回到客户端。Reset @@ROWCOUNT to 0 but do not return the value to the client.

执行简单分配的语句始终将 @@ROWCOUNT 值设置为 1。Statements that make a simple assignment always set the @@ROWCOUNT value to 1. 不将任何行发送到客户端。No rows are sent to the client. 这些语句的示例包括:SET @local_variable、RETURN、READTEXT 和在没有 SELECT GETDATE() 或 SELECT 'Generic Text' 等查询语句的情况下选择。Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

在查询中进行分配或使用 RETURN 的语句将 @@ROWCOUNT 值设置为受查询影响或由查询读取的行数,例如:SELECT @local_variable = c1 FROM t1 。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.

数据操作语言 (DML) 语句将 @@ROWCOUNT 值设置为受查询影响的行数,并将该值返回到客户端。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. DML 语句不会将任何行发送到客户端。The DML statements may not send any rows to the client.

DECLARE CURSOR 和 FETCH 将 @@ROWCOUNT 值设置为 1。DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE 语句保留前一个 @@ROWCOUNT。EXECUTE statements preserve the previous @@ROWCOUNT.

USE、SET <选项>、DEALLOCATE CURSOR、CLOSE CURSOR、PRINT、RAISERROR、BEGIN TRANSACTION 或 COMMIT TRANSACTION 等语句将 ROWCOUNT 值重置为 0。Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

本机编译存储过程保留以前的 @@ROWCOUNT。Natively compiled stored procedures preserve the previous @@ROWCOUNT. 本机编译存储过程中的 Transact-SQLTransact-SQL 语句不设置 @@ROWCOUNT。Transact-SQLTransact-SQL statements inside natively compiled stored procedures do not set @@ROWCOUNT. 有关详细信息,请参阅本机编译的存储过程For more information, see Natively Compiled Stored Procedures.

示例Examples

以下示例执行 UPDATE 语句并使用 @@ROWCOUNT 来检测是否更改了任何行。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  

另请参阅See Also

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