Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SET NOCOUNT OFF - how to capture returned messages

Posted on 2009-05-09
6
Medium Priority
?
569 Views
Last Modified: 2012-06-27
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) ??
0
Comment
Question by:campinam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 24346650
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24347000
I agree with that method being the best!
0
 

Author Comment

by:campinam
ID: 24347004
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 24347984
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24348881
If you have SET NOCOUNT OFF you can use ExecteNonQuery to get the value, however it will only be the last one.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 24348900
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Six Sigma Control Plans

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question