Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
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
Updated 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!

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.
 
Share this answer
 
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.
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.
 
Share this answer
 
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900