I'm always a bit concerned when doing an update statement in case there is a
bug that causes it to update an entire table or too many records. Out of all
the programming techniques the update statement would have to have close to
the worst concequences for a bug, imo. I'm curious what techniques people
use to avoid this problem (besides the obvious such as testing).

The reason I'm asking is I found an update statement which should have had a
"where ID = @ID" but I just plain forgot the where clause. This went out to
customers but through some miracle never got called. It was within a couple
of If statement and was only called in unusual circumstance which luckily
never happened.

Cheers,
Michael

Michael C wrote:
Show quoteHide quoteI'd say there was a serious gap in testing that particular procedure :-)
Every procedure should be tested with all possible inputs and the
outputs clearly examined and documented. As a part of the stored
procedure development process, the developer should clearly examine the
procedure code and design a set of test calls that will attack all of
the source. Also make sure that NULL parameter values are tested as well
where allowed. This could result in a lot of test calls, but it's much
easier to set this up during development than it is to design during an
application test. As you saw, it possible an application will never call
a stored procedure with the parameters that trigger a major problem. In
that case, the development side is the only way to catch these problems.

This could be an internal process where developers clearly document the
test cases (call text and expected outpu). You could document these
items along with the stored procedure text right in your version control
system. Another option is to use a framework for testing that can help
you automate unit testing. An open-source project call TSQL Unit is such
an option (although it hasn't been updated in a while):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04i1.asp
http://sourceforge.net/projects/tsqlunit

That's not to say an error can't creep in occasionally, but a documented
development and testing process will go a long way in eliminating these
types of errors.

You live in a very different world to me :-) Our projects are fairly rushed
and disorganised and testing is fairly poor. I'd love it to be different but
the only way to do that is change jobs.

Michael

On Mon, 22 Aug 2005 13:57:38 +1000, Michael C wrote:

(snip)
Hi Michael,

But isn't the amount of time you have to spend repairing things even
more than the time you would have spent testing?

Best, Hugo

That's a good question and I'd probably say yes but it's just the way it is
here. There's never any time to do it properly but there's always plenty of
time to fix it. There's always someone who needs something urgently and
always a reason to rush it. I keep explaining that if we'd been doing this
three years ago there'd be someone who needed it urgently then but no one
seems to listen to that. Nothing here get approved if it's over 2 months, it
doesn't matter if it runs over time as long the initial estimate is for 2
months or less. I could go on for hours about this. :-)

Michael

Add this to your approval form:
1. This job must be done quick...
2. This job must be well done...
3. This job must be done cheap...
PICK 2 OUT OF 3.

Too bad a lot of bosses choose 1 and 3.

Show quoteHide quote

On Thu, 25 Aug 2005 09:57:20 +1000, Michael C wrote:

By all means do!

(But be sure to finish in no more than two months)

Best, Hugo

doesn't matter if it runs over time as long the initial estimate is for
2
months or less. <<

makes sense. big projects are more likely to get outsourced

My post errored out so I am not sure if my earlies response got posted.

You could put begin tran before you update and commit or rollback tran after
checking your results.

Check out the following example:

set nocount on
go
create table test(
c1 int not null,
c2 int not null)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
go
select * from test
go
begin tran
update test set c2= 333 where c1=2
select * from test
/*
c1          c2         
----------- -----------
1           1
2           333
3           1
*/
rollback tran
select * from test
/*
c1          c2         
----------- -----------
1           1
2           1
3           1
*/


go
drop table test
go


HTH....


Show quoteHide quote

checking your results.
<<

oh yes, sure, and have a two hour lunch break in the midddle of your
checking

;)

On Fri, 19 Aug 2005 11:52:02 +1000, Michael C wrote:

Hi Michael,

I'd say that the DELETE is possibly even more dangerous.

In one of my first jobs in the SQL Server world, my boss was writing and
testing a query to remove erroneous rows from the production database
that were introduced by a bug. Here's how he tested his delete statement
to check that he got the where clause just right:

-- DELETE FROM TheTable
select * from TheTable
WHERE .....
AND .....
go

Once he had the wherer clause tweaked to return just the rows that had
to be deleted, he removed the comment in front of the first line and
clicked the execute button....

After a few minutes, he asked my more experienced coworker if he
understood why the query was taking so long. The coworker then
immediately rushed to his own PC and issued a kill command.

I think that this was the only time that we were actually glad that this
table was loaded with trigger code that was slower than a turtle with
two crippled legs.


For ad-hoc queries that I want to test first, I always START to type
this:

BEGIN TRAN
go

go
ROLLBACK TRAN
go

After that, I position the cursor between the two go's and start typing
the selects that show the result of my statement, and then the update,
insert, or delete statement itself.

(The extra go before the ROLLBACK ensures it gets executed even if the
query has an error that aborts the batch)

Best, Hugo

Show quote Hide quote We had a similar problem except much worse. One particular database had no
referential integrity and someone thought they'd issue a delete statement to
delete orphan records. Problem was they forgot the where clause altogether
and deleted all the records in the table. This went out to 300 customers but
I think the problem was found and fixed fairly quickly so not too many
people encountered the problem. I pointed out that the delete would have
failed if we had integrity but it seemed to fall on deaf ears.

Michael

Michael C wrote:
Hows the job hunting going? Seriously - do your customers know your
name, does your name get associated with these problems? Sooner or
later (probably), your customers are going to move to a supplier who
does care to get these things right in the first place. I'd get out
before the going gets bad (but make sure to include a good lengthy
sermon in your notice)

Just my two-penneth.

Damien

I don't think that will be so much of a problem because I doubt I'll
encounter any of this company's customers while looking for another job. But
what I think is a problem is that I have a less impressive resume. I'm a
good enough programmer to work anywhere but am lacking in the project
management side of things and that really shows in an interview. It also
helps if you've worked for well known companies, interviewers always ask
where a company is you've work for and how many employees they have.

Michael

[OT] SQL Humor
composite key structure
Default Column Values
Updating Tables with Computed Columns - SET ARITHABORT
Need Query Help