SQL 如何获取受存储过程影响的记录数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1201358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the number of records affected by a stored procedure?
提问by dthrasher
For INSERT, UPDATEand DELETESQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1.
对于直接针对数据库执行的INSERT,UPDATE和DELETESQL 语句，大多数数据库提供程序返回受影响的行数。对于存储过程，受影响的记录数始终为-1。
How do we get the number of records affected by a stored procedure?
我们如何获得受存储过程影响的记录数？
采纳答案by OMG Ponies
Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNTif using SQL Server. Use SQL%ROWCOUNTif you are using Oracle.
为存储过程注册一个输出参数，并根据@@ROWCOUNT是否使用 SQL Server设置值。使用SQL%ROWCOUNT，如果你使用的是Oracle。
Mind that if you have multiple INSERT/UPDATE/DELETE, you'll need a variable to store the result from @@ROWCOUNTfor each operation.
请注意，如果您有多个INSERT/UPDATE/DELETE，则需要一个变量来存储@@ROWCOUNT每个操作的结果。
回答by Raj More
@@RowCountwill give you the number of records affected by a SQL Statement.
@@RowCount将为您提供受 SQL 语句影响的记录数。
The @@RowCountworks 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.
在@@RowCount只有当你发出它随即工作。因此，如果您要捕获错误，则必须在同一行上进行。如果你把它分开，你会错过第二个。
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
回答by Dude Pascalou
Turns out for me that SET NOCOUNT ONwas set in the stored procedure script (by default on SQL Server Management Studio) and SqlCommand.ExecuteNonQuery();always returned -1.
对我来说，这SET NOCOUNT ON是在存储过程脚本中设置的（默认情况下在 SQL Server Management Studio 上）并且SqlCommand.ExecuteNonQuery();总是返回 -1。
I just set it off: SET NOCOUNT OFFwithout needing to use @@ROWCOUNT.
我只是将其关闭：SET NOCOUNT OFF无需使用@@ROWCOUNT.
More details found here : SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete
在此处找到更多详细信息：SqlCommand.ExecuteNonQuery() 在执行插入/更新/删除时返回 -1
回答by Bob Mc
For Microsoft SQL Server you can return the @@ROWCOUNTvariable to return the number of rows affected by the last statement in the stored procedure.
对于 Microsoft SQL Server，您可以返回@@ROWCOUNT变量以返回受存储过程中最后一条语句影响的行数。
回答by Fredou
回答by Michael Kingsford Gray
WARNING: @@ROWCOUNTmay return bogusdata if the table being altered has triggersattached to it!
警告：如果正在更改的表附加了触发器，则@@ROWCOUNT可能会返回虚假数据！
The @@ROWCOUNTwill return the number of records affected by the TRIGGER, not the actual statement!
在@@ROWCOUNT返回的受动了扳机，而不是实际的语句记录数！
