Click here to Skip to main content
12,297,734 members (25,061 online)
Rate this:
 
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 1-Feb-12 7:38am
   
I am afraid, you are wrong here. See my comment below.
Tech Code Freak 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 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 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 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 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 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 1-Feb-12 8:24am
   
Thanks. Glad it helped.
Tech Code Freak 1-Feb-12 6:51am
   
5up for the conversation!
Rajeev Jayaram 1-Feb-12 7:37am
   
Thanks.
thatraja 1-Feb-12 11:49am
   
5!
Rajeev Jayaram 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 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 1-Feb-12 8:16am
   
You're welcome.
thatraja 1-Feb-12 11:48am
   
Spot on, 5!
Amir Mahfoozi 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
Top Experts
Last 24hrsThis month


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100