Database Upserts - Good or Bad Practice?

Question

Looking for some insight as to whether an Upsert (insert or if exists, then update) procedure is considered bad practice in database programming. I work in SQL server if that bears any relevance.

At a place I worked some months ago, the the resident DB guru stated in newly written db coding standards (most of which I agreed with), that Upserts should be avoided.

I can't really see a logical reason for this, and consider my self reasonably conscious of good programming practice. I think they are useful for straight forward data management and help to avoid excessive stored procedure numbers.

Looking for some insight / discussion that will help me come to a conclusion on this.

Thanks.

Update In response to comments:

The specific context I refer to is the creation or update of a domain entity data representation in the database. Say for example a "Person" object exists as a representation of the "Person" table in the database. I simply need a mechanism for creating a new Person, or updating an existing one. Here I have the option of creating an Upsert stored procedure, or two separate stored procedures - one for Update, and one for Insert.

Any advantages or disadvantages in anyones view?

Answer 1

The primary problem is overwriting an existing record when the intention is to add a new record because whatever was selected as the key is duplicated. Say a login name for example. You see that login exists so you update when you should have kicked back an error that the login is a duplicate.

A second problem is with resurrecting a deleted record. Say process "A" queries the record, process "B" deletes it, and then process "A" submits a change. The record that was intended to be deleted is now back in the database rather than passing an exception back to "A" that it was deleted.