重写行受到影响在SQL Server中使用的ExecuteNonQuery？ [英] Overriding rows affected in SQL Server using ExecuteNonQuery?

问题描述

我有拉一些数据到几个表变量，然后根据这些数据做了一些插入到几个表的INSERT语句。我只关心那个被插入到真实的表，而不是表变量中的行，但的ExecuteNonQuery将返回所有@@ ROWCOUNT的总和。我想知道有没有办法来覆盖正在使用的ExecuteNonQuery返回的行数？

我知道我可以使用的ExecuteScalar或输出变量作为替代。

下面是沸腾它归结为一个简单的例子，一个例子：

  CREATE TABLE VersionExample（版本VARCHAR（255））

声明@RowCountICareAbout INT

DECLARE @举例表（版本VARCHAR（255））

INSERT INTO @举例SELECT @@ VERSION

INSERT INTO VersionExample选择版本从@举例

SET @RowCountICareAbout = @@ ROWCOUNT

--use @RowCountICareAbout为受影响的行返回的ExecuteNonQuery
 

解决方案

不知道这是否会工作，但你试过SET NOCOUNT ON（然后设置NOCOUNT OFF最终查询之前）？

更新：这个博客帖子和评论似乎表明，这将确实工作：

<一个href="http://petesbloggerama.blogspot.com/2006/10/note-to-self-set-nocount-on-not.html">http://petesbloggerama.blogspot.com/2006/10/note-to-self-set-nocount-on-not.html

I have an insert statement that pulls some data into a few table variables and then based on that data does a few inserts into several tables. I only care about the rows that are inserted into the real tables and not the table variables, but ExecuteNonQuery will return the sum of all @@ROWCOUNT's. What I would like to know is there a way to override the rowcount that is returned using ExecuteNonQuery?

I am aware that I can use ExecuteScalar or output variables as an alternative.

Here is an example that boils it down to a simple example:

CREATE TABLE VersionExample ( Version Varchar(255) )  

Declare @RowCountICareAbout int

DECLARE @Example TABLE ( Version Varchar(255) )  

INSERT INTO @Example Select @@VERSION

INSERT INTO VersionExample SELECT Version FROM @Example

SET @RowCountICareAbout = @@ROWCOUNT

--Use @RowCountICareAbout as the rows affected returned to ExecuteNonQuery

解决方案

No idea if this will work, but have you tried SET NOCOUNT ON (and then SET NOCOUNT OFF before your final query)?

Update: this blog post and comments seem to indicate this will indeed work:

http://petesbloggerama.blogspot.com/2006/10/note-to-self-set-nocount-on-not.html

这篇关于重写行受到影响在SQL Server中使用的ExecuteNonQuery？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！