Update sprocs and rowsaffected

[Greg]

Not sure if this post belongs here but here goes.

We call DataAdapter.Update where the CommandText is an sproc name. Inside
the sproc, we have numerous IF statements. I was recently told these IF
statements would cause the sproc to recompile each time it is called. is
this correct?

We use the IF statements to compare the original value of a field to the
value passed in (the current value). If they are different, we update the
field. There are 160 fields in this record, so we have at least 160 IF
checks.

I am also informed that every time we use a SELECT statement, if a record is
returned, RowsAffected is set to 1. So, if an update fails, we still see
the value 1 on the return from the call to Update. How does one work around
this?

 

[Mary Chipman]

Is the first line in your sproc SET NOCOUNT ON? The default is off,
which means that each SELECT returns a result set as well as a done in
proc message (the number of records affected). This also results in an
additional round trip across the network, and is the source of many
bugs. However, SET NOCOUNT ON has no effect on @@rowcount, which you
can use inside your sproc for branching. See SQL BooksOnline for more
info. If you want to find out if your sproc is recompiling, use the
Profiler to capture the SP:Recompile, SP:StmtStarting and SP:Completed
events.

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[Greg]

We are using sql server 7.0. I do not see sp compile or recompile in the
events list.

 

[Greg]

I read the docs as suggested:

"When SET NOCOUNT is ON, the count (indicating the number of rows affected
by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the
count is returned.The @@ROWCOUNT function is updated even when SET NOCOUNT
is ON. SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the
client for each statement in a stored procedure. When using the utilities
provided with Microsoft® SQL ServerT to execute queries, the results prevent
"nn rows affected" from being displayed at the end Transact-SQL statements
such as SELECT, INSERT, UPDATE, and DELETE. For stored procedures that
contain several statements that do not return much actual data, this can
provide a significant performance boost because network traffic is greatly
reduced. The setting of SET NOCOUNT is set at execute or run time and not
at parse time."
The first thing our update sproc does is a SELECT to retrieve the record to
be updated from sql server. It then assigns the 'original' values to
variables declared inside the sproc. We then start comparing the input param
value to the internal sproc variable.

If I SET NOCOUNT ON at the start of the sproc, I assume I need to SET
NOCOUNT OFF right before the UPDATE and SET statements start? I assume yes
so the call to Update will return 1 RowsAffected.

Now, after a successful update, inside the same sproc, we do 3 more SELECTS
for output params. Do I need to SET NOCOUNT back 'off' prior to those
selects?

My concern is, if I turn it ON at the start of the sproc, then back OFF
before the update then back ON after the update I will not know the
rowsaffected from the actual update. Does it keep the value stored when you
call ON the increment it after you call OFF? Or does rowsaffected reset to
0 each time you call ON?



Thanks

 

[William \(Bill\) Vaughn]

Another approach is to capture the @@rowsaffected value in a local variable
and SET NOCOUNT OFF for the entire procedure. At the end, SET one of your
OUTPUT parameters to the local variable to pass back to your application.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Greg]

Bill, when would I set the local variable? I am also concerned about
DataAdapter.Update returning the RowsAffected. If I 'bypass' this, how can
I catch DBConcurrencyExceptions?

My ultimate goal is to only check RowsAffected for the actual update. If its
1, I know the update was successful. If it's 0, I know it was not.
Currently I always get a 1 with all the SELECTS in the sproc.

Ultimately, I'd like to know the best practice for the 'internals' of an
update sproc when using a sproc for the UpdateCommand in a DataAdapter,
since the call to Update returns RowsAffected

 

[William \(Bill\) Vaughn]

Yes, that's what I'm getting at. In order to use the Update method, as you've discovered, you're going to have to use it's (fairly inflexible) technique to signal that a collision occurred. The DataAdapter and its methods are tuned for simple cases and "denormalized" data. Most of us can't get the performance or functionality we need using this approach.

As I see it you can:
1.. Fall back and use your own code to call the SP that does the Update. In this case you replace the single line of code that calls the Update against the DataAdapter with a ExecuteNonQuery command call that invokes your own SP. After that you can test the OUTPUT parameter or the RETURN value integer for success, failure or something in between. You can also "signal" back with RAISERROR messages and exception codes too. Yes, you'll still have to roll your own Parameters collection and (possibly) post the current data back into the DataSet if you're using that approach. Once you write your own "DataAdapter" class and implement your own "Update" method you can use whatever mechanism makes sense. That's the advantage of the .NET technology--virtually anything is possible and a lot easier to do than it was earlier.
2.. Attempt to figure out how to set the @@ROWCOUNT global variable to 1 or 0 based on the success or failure of your SP. One hack that I've seen used is to use an action query that does a SELECT TOP 1 against any table--this sets the @@ROWCOUNT to 1 and the "RowsAffected" value as well.
Many folks eventually gravitate to option 1 as they run up against the stops in ADO.NET.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________