如何获取受存储过程影响的记录数？

对于直接对数据库执行的 INSERT、 UPDATE和 DELETESQL 语句，大多数数据库提供程序返回受影响的行数。对于存储过程，受影响的记录总是 -1。

我们如何得到受存储过程影响的记录数？

175978 次浏览
小开

For Microsoft SQL Server you can return the @@ROWCOUNT variable to return the number of rows affected by the last statement in the stored procedure.

小开

@@ROWCOUNT

小开
最佳答案

Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.

Mind that if you have multiple INSERT/UPDATE/DELETE, you'll need a variable to store the result from @@ROWCOUNT for each operation.

小开

@@RowCount will give you the number of records affected by a SQL Statement.

The @@RowCount works only if you issue it immediately afterwards. So if you are trapping errors, you have to do it on the same line. If you split it up, you will miss out on whichever one you put second.

SELECT @NumRowsChanged = @@ROWCOUNT, @ErrorCode = @@ERROR

If you have multiple statements, you will have to capture the number of rows affected for each one and add them up.

SELECT @NumRowsChanged = @NumRowsChanged  + @@ROWCOUNT, @ErrorCode = @@ERROR
小开

WARNING: @@ROWCOUNT may return bogus data if the table being altered has triggers attached to it!

The @@ROWCOUNT will return the number of records affected by the TRIGGER, not the actual statement!

小开

Turns out for me that SET NOCOUNT ON was set in the stored procedure script (by default on SQL Server Management Studio) and SqlCommand.ExecuteNonQuery(); always returned -1.

I just set it off: SET NOCOUNT OFF without needing to use @@ROWCOUNT.

More details found here : SqlCommand.ExecuteNonQuery Method#Remarks

When SET NOCOUNT ON is set on the connection (before or as part of executing the command, or as part of a trigger initiated by the execution of the command) the rows affected by individual statements stop contributing to the count of rows affected that is returned by this method.
If no statements are detected that contribute to the count, the return value is -1. If a rollback occurs, the return value is also -1.


提交答案