retrieving individual row counts from a stored procedure

[Andy Fish]

(this is related to my previous post yesterday but I think i'm asking a
distinct question so it's a new post)

say I have a stored procedure that does 3 updates:

update table1 set .... where ...
update table2 set .... where ...
update table3 set .... where ...

if I execute this using management studio, the output window shows (for
instance):

(1 row(s) affected)
(5 row(s) affected)
(10 row(s) affected)

however, if I execute it in ado.net using ExecuteReader, I just get a single
resultset with 16 rows affected. RxecuteNonQuery and ExecuteScalar don't
help either.

is there any way at all to get the individual row counts from ado.net?

Thanks

Andy

 

[Dan Guzman]

is there any way at all to get the individual row counts from ado.net?

The ExecuteReader.RowsAffected property will contain the number of rows
affected. Invoke the SqlDataReader.NextResult method to navigate the the
subsequent result sets. Untested example:

//print row counts of each update command (assuming SET NOCOUNT OFF)
reader = command.ExecuteReader();
do
{
Console.WriteLine(string.Format("({0} row(s) affected)",
reader.RowsAffected.ToString()));
} while (reader.NextResult());

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

 

[Andy Fish]

Dan,

Unfortunately this is exactly what I'm doing,

It's only returning a single result set with the RowsAffected as the total.

FWIW I'm using .Net framework 2.0 with sql server 2005 express

Andy

 

[Plamen Ratchev]

If you run 'SELECT @@ROWCOUNT AS cnt' after each update statement then those
should come as separate result sets that you can read in ADO.NET.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 

[Cowboy \(Gregory A. Beamer\)]

In your sproc, set up output parameters for the row counts you want. Then
set each output parameter using:

SET @parameterName = @@ROWCOUNT

You can then pull the output parameters and see what was affected. This is
more precise than simply firing the full sproc and see the RowsAffected.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Dan Guzman]

It's only returning a single result set with the RowsAffected as the

total.

My appologies for the misinformation I posted earlier. That's what I get
for trusting my memory instead of actually reading the documentation and
running a test :-(

I think the only ways to get the individual counts is using either a result
set or output parameters as Plamen and Cowboy suggested. A variant of the
result set technique is to return the counts in a single result set:

UPDATE ...
SET @count1 = @@ROWCOUNT
UPDATE ...
SET @count2 = @@ROWCOUNT
UPDATE ...
SET @count3 = @@ROWCOUNT
SELECT @count1 AS RowsAffected
UNION ALL @count2
UNION ALL @count3

or

DECLARE TABLE @RowsAffected(RowsAffected int)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
SELECT RowsAffected FROM @RowsAffected






--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/