Overriding rows affected in SQL Server using ExecuteNonQuery?

Question

I have an insert statement that extracts some data into several table variables, and then, based on this data, inserts several attachments into several tables. I only care about rows inserted into real tables, not table variables, but ExecuteNonQuery will return the sum of all @@ ROWCOUNT. I would like to know if there is a way to override the number of rows returned with ExecuteNonQuery?

I know that as an alternative, I can use ExecuteScalar or output variables.

Here is an example that reduces it 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 

Answer 1

I don't know if this will work, but did you try SET NOCOUNT ON (and then SET NOCOUNT OFF before the final request)?

Update: This blog post and comments seem to indicate that this will actually work:

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

Answer 2

No, it is not possible to override or change this behavior in ADO.NET or SQL Server.

The only option you have is to capture the count of the lines that interest you, and put them in a variable and return them.