Number of Updated Rows

I want to get the number of rows updated by an update statement in a stored procedure. Is there a way to do this?

Example:

Declare @.NumRow int;

set @.NumRow = udpate table set Name = 'John Doe' where ID = 123456789;

print @.NumRow;

The above doesn't work.

use the @.@.ROWCOUNT internal; something like:

declare @.updateRows integer

UPDATE yourTable
SET ...
WHERE ...
SET @.updateRows = @.@.ROWCOUNT

Dave

|||

Oh yea, a hidden gotcha:

You need to be careful and make sure that the @.@.ROWCOUNT internal is used IMMEDIATELY after the update statement so that its value does not change. You may get surprised if you have any steps between your update statement and where you used @.@.ROWCOUNT.

Dave

|||Yeah, @.@.rowcount is very good that way.

Something else you might want to be aware of is that you can output the rows from an update query. This gives you the chance to do all kinds of extra analysis on what's changed - not just the number of rows, but all kinds of other details too.

Have a look through http://msdn2.microsoft.com/en-us/ms177564.aspx to see some of the things you can get up to.

Robsql