Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL
Hello everyone!
 
Just now, I have read this Article[^] and have a doubt how to use @@ROWCOUNT to get affected number of rows keeping NOCOUNT ON.
 
I have tagged this in ASP.Net(C#) as I am using it as the front end and need some guidance whether I can do this or not.
 
I want to know the number of rows affected by the stored procedures and accordingly execute the codes. So, is there any way to know the number of rows affected by a stored procedure even after keeping NOCOUNT ON?
If yes, then which one?
 
Please help..
Posted 31-Jan-12 23:43pm
Edited 1-Feb-12 1:56am
v3
Comments
Rajeev Jayaram at 1-Feb-12 7:38am
   
I am afraid, you are wrong here. See my comment below.
Tech Code Freak at 1-Feb-12 7:58am
   
Okay, after reading through the link provided in your comment below, I have got some answer!
Thanks again for that!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The article clearly states,
 
Quote:
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.
Return the ROWCOUNT[^] value from the stored procedure and use it in the front end.
  Permalink  
v2
Comments
Tech Code Freak at 1-Feb-12 6:13am
   
Okay, I have already read that article.
I just want to know how to use it in the front end. i.e. How will the front end come to know the number of rows affected by the stored procedure with NOCOUNT ON using @@ROWCOUNT?
Rajeev Jayaram at 1-Feb-12 6:25am
   
Refer this link:
http://msdn.microsoft.com/en-us/library/aa259204%28v=sql.80%29.aspx
 
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.
 
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
Tech Code Freak at 1-Feb-12 6:40am
   
Okay!
Now I have come to know that if I want to get the number of rows affected by a stored procedure in the front end, I have to compulsorily SET NOCOUNT OFF.
And that I can't get any idea of the affected rows if I SET NOCOUNT ON. As I have tried to RETURN @@ROWCOUNT after setting NOCOUNT ON; still nothing is returned. So, the Solution is that if you want to know the number of rows affected by a stored procedure in the Front end, you have to SET NOCOUNT OFF.
 
Thanks!
Rajeev Jayaram at 1-Feb-12 7:37am
   
I am afraid, you are wrong here. Even if SET NOCOUNT is ON you can return @@ROWCOUNT.
 
See this blog for more details.
http://blog.sqlauthority.com/2011/12/02/sql-server-effect-of-set-no-count-on-rowcount/
Tech Code Freak at 1-Feb-12 8:07am
   
Yes, I was wrong.
This link is very helpful!
Thanks!
Now I have got the solution viewing the link in Amir's Answer. That was exactly what I wanted.
Thanks to you too for this link!
 
And now I will delete all the wrong things and statements said by me in the question as well as comments for others.
Rajeev Jayaram at 1-Feb-12 8:24am
   
Thanks. Glad it helped.
Tech Code Freak at 1-Feb-12 6:51am
   
5up for the conversation!
Rajeev Jayaram at 1-Feb-12 7:37am
   
Thanks.
thatraja at 1-Feb-12 11:49am
   
5!
Rajeev Jayaram at 2-Feb-12 2:01am
   
Thanks Raja.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

After your comments for Rajeev :
 
Read this page and you will find your answer :
 
http://msdn.microsoft.com/en-us/library/59x02y99%28v=vs.110%29.aspx[^]
 
Hope it helps.
  Permalink  
Comments
Tech Code Freak at 1-Feb-12 8:01am
   
Thanks a lot!
This is exactly what I wanted!

 
Viewing the link provided by You and that in Rajeev's comment, I have got my Question Solved!
Amir Mahfoozi at 1-Feb-12 8:16am
   
You're welcome.
thatraja at 1-Feb-12 11:48am
   
Spot on, 5!
Amir Mahfoozi at 2-Feb-12 3:31am
   
Thanks a lot Thatraja :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 324
1 Sergey Alexandrovich Kryukov 182
2 Maciej Los 159
3 Hard_Rockz 143
4 Richard MacCutchan 125
0 OriginalGriff 5,374
1 Sergey Alexandrovich Kryukov 4,713
2 Peter Leow 2,944
3 DamithSL 2,465
4 Maciej Los 2,270


Advertise | Privacy | Mobile
Web02 | 2.8.140718.1 | Last Updated 1 Feb 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid