How to return No. of rows updated in SP

Question

Post reply

How to return No. of rows updated in SP

Gary Dale

Old Hand

Points: 345
More actions
July 28, 2005 at 8:30 am

#86970

Hi guys, I have a simple SP that takes a set of parameters equal to the columns in a table and attempts to perform an update to a single record in that table. I test the value of the timestamp column in the parameters against the timestamp column in the table and if the are the same I perform the update.
Now, if the timestamp values are different then another user has performed an update since the current user got their data. So, how can I return a value that will indicate whether the update succeeded? The value could be the number of records affected (one will be the maximum) or a boolean value.
Regards

Viewing 11 posts - 1 through 10 (of 10 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: 345 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: 5163 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