CODEWITHSUNDEEP
.netsql-serversql-server-2008ado.net
John Lemp
John Lemp

Reputation: 5067

Overriding rows affected in SQL Server using ExecuteNonQuery?

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

Upvotes: 2

Views: 1507

Answers (2)

Phil Sandler
Phil Sandler

Reputation: 28046

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

Upvotes: 5

marc_s
marc_s

Reputation: 755073

No, there is no way to override or alter that behavior in ADO.NET or SQL Server.

The only option you have is to capture the row counts that interest you and put those into a variable and return them.

Upvotes: 0

Related Questions