concurrency - Rows affected (getting the right one from a stored procedure)

[Jeff Jarrell]

I know this may be a sql thing but I would expect someone to have dealt with
this.

I have some update stored procedures with many steps. normally, my stored
procedures "set nocount off".

so the rowsAffected = ExecuteNonQuery doesn't return anything but 0. So I
turn it on (nocount off), but it is very easy to mess up what step has the
nocount on\off (as time goes on, changes, etc) . The proper "Rows Affected"
might be the first step, might be the last step, might be somewhere in
between.

In my stored procedure I'd like to capture the row's affected in the right
step, and then force the proper return at the end of the stored procedure.

Any ideas on this?

thanks,
jeff

 

[Val Mazur \(MVP\)]

Hi Jeff,

You could query @@ROWCOUNT variable using SELECT @@ROCOUNT. It will return
you number of the affected records, but it return value just for the last
statement. If you need to get accumulated value, then you would need to
query it after each statement and sum it up

 

[Jeff Jarrell]

It's great your online to help my poor tired eyes.

Now with your help, I am able to simple do a
select @iRowCount = @@ROWCOUNT
after the step that I want to capture, then return that value at the end.

easy enough, thanks,
jeff