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

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
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"Jeff Jarrell" <(E-Mail Removed)> wrote in message
news:uFZLVs$(E-Mail Removed)...
>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
>
>
>
>
>
>

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

"Val Mazur (MVP)" <(E-Mail Removed)> wrote in message
news:uxC%23i2$(E-Mail Removed)...
> 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
> --
> Val Mazur
> Microsoft MVP
>
> http://xport.mvps.org
>
>
>
> "Jeff Jarrell" <(E-Mail Removed)> wrote in message
> news:uFZLVs$(E-Mail Removed)...
>>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
>>
>>
>>
>>
>>
>>
>
>