SET NOCOUNT OFF - how to capture returned messages

Suppose I SET NOCOUNT OFF in a stored procedure; I know it's not recommended, but just suppose that for a moment.

The stored procedure performs numerous deletes, updates, inserts. After each of those a message is returned to the caller (in this case ADO.NET) containing the number of records affected.

How do I capture, in ADO.NET, such returned messages (a large number of such messages) ??
campinamAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can use Output parameters to return the rows affected

create proc sample
@i int ,
@name varchar(10) ,
@rowsAffected1 int output,
@RowsAffected2 int output
as
SET NOCOUT ON

INSERT into....VALUES (... )
SET @RowsAffected1 = @@ROWCOUNT

UPDATE SomeTabel ...
SET @RowsAffected2 = @@ROWCOUNT
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with that method being the best!
0
 
campinamAuthor Commented:
Thank you!
Can I capture the same info (rows affected) using SET NOCOUNT OFF and messages it returns (this was the point of my question) - how do I capture messages caused by SET NOCOUNT OFF (maybe that's not possible??)
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, that is indeed not possibly directly.
anyhow, as the parameter method is much easier to port to another database system, I would go that method anyway...
0
 
Anthony PerkinsCommented:
If you have SET NOCOUNT OFF you can use ExecteNonQuery to get the value, however it will only be the last one.
0
 
Anthony PerkinsCommented:
And it would help if I could type.  It should be ExecuteNonQuery.  :)

I should have also stated that this only applies to INSERT, UPDATE and DELETE.  All other statements return -1.

And it should be clarified (in case it was not obvious) that if you have SET NOCOUNT ON then ExecuteNonQuery will not reurn any value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.