How to return No. of rows updated in SP

Question

Post reply

How to return No. of rows updated in SP

Viewing 10 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Select @@rowcount -- after the Update, insert or delete statement

* Noel

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

Obviously you're using the SET NOCOUNT ON option inside your SP (arent you ), the good news is that this doesn't stop the @@rowcount from working as I've heard people suggest in the past (no here I hasten to add)

And I suppose if you want to be really pedantic you should also include the SET NOCOUNT OFF at the end of the SP too.

Gary Dale Old Hand Points: 375 More actions · Answer 3

Thank you for that.

What's the precise effect of using SET NOCOUNT ON?

Regards

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

Whenever you run a query that selects/inserts/deletes or updates any information you get a message along the lines of x rows affected

Setting the NOCOUNT option ON removes this message and reduces the data being transferred to the client.

Whilst this is not a huge saving for an individual query it starts to become significant as the number of queries increases.

noeld SSC Guru Points: 96590 More actions · Answer 5

It will reduce network traffic by not sending the count of affected records of each statement back to the client (in a separated stream )

* Noel

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

You don't have to SET NOCOUNT OFF at the end of a stored procedure because that ends the batch and the value of any previous SET will be restored.

There is no "i" in team, but idiot has two.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 7

I agree 100%, that's why I said only to do it if you were being pedantic

LOL

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 8

Mike: Only the true pedant will always tell you why you should do something, so I did.

There is no "i" in team, but idiot has two.

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

Oops - guess I'm guilty as charged then! I just posted a stored procedure that set the nocount off at the end...

ps: In my case I think I just prefer to have it said that I like to finish whatever I start...so I'll let one of you take the title!

**ASCII stupid question, get a stupid ANSI !!!**

Michael Du Bois SSCertifiable Points: 5193 More actions · Answer 10

Sometimes procedures are like children they know what they should do but it always better to tell exactually what you want. if you start it finish it. an extra line of code will never kill you.

Yea annual retenative has been mentioned with my name

Mike