Is Upsert a violation of the principle of single responsibility?

Question

I like to use Upsert stored procedures that update records if they exist, or insert them if they do not. Without them, I will need to first find out if the record exists, and then have two separate stored procedures, which I would call based on the result.

I have never thought about a problem until today when I created a stored procedure called UpdateOrDeleteRow. As soon as I found that I included “Or” in the name, my SRP carrier spider started kicking, and I realized that upserts are basically the same thing.

Is this a violation of SRP? If so, is that acceptable? If not, what should I do?

I understand that SRP is an OOP principle, and T-SQL is not an OOP language, but the basics for the principle seem to apply here.

Answer 1

Personally, I do not believe that this principle is fully applied in SQL Server. Stored procedures do not always perform only one action (and I think the notion that a stored procedure is equivalent to a class is erroneous). I don’t think it makes sense to split each expression in a stored procedure into its own stored procedure. You can become absolutely ridiculous with this.

Of course, there is a balance, because you can be funny in a different way. You do not want a stored procedure with 18 different ways to specify parameters so that it can do 540 different things based on combinations.

For UPSERT, I still suggest that one stored procedure is in order. If you want it to be better about this in order to fulfill one goal, change the update / paste into one MERGE . :-) However, be very careful with MERGE .

Answer 2

There is another principle that I like more than SRP - DRY . So, if you call this sequence in one place, you can think of one responsibility. But when you repeat the same sequence of actions several times, DRY forces me to remove duplication.

By the way. Just think that you can avoid OR in the name of the procedure / method. The UpdateOrInsert operation has a very good name Save . I think this does not violate SRP.

Answer 3

If you already have processes that perform the Update or Delete operations yourself, with the hope of logging for audit purposes, you could request an upsert proc call individually. Thus, only those procs do the job, which should help keep things manageable, even if they are called from several places.

Answer 4

In principle, a single responsibility states that an object should have only one reason for change. The only reason you should modify the Upsert stored procedure is because the table structure has changed. That way, I think you're fine when creating upsert stored procedures.

Answer 5

I would not agree that the basic principle should be applied in this case, since it does some kind of redundant code in your code.

First, let's look at what your UPSERT does, it checks to see if there is data based on whether it performs an INSERT or UPDATE.

In the code for this you need to make 2 calls in your database, depending on how your application is structured, it can also mean opening and closing two connections.

So, you have 3 methods in your code (one for executing each proc), then a method for calling each of these methods and logic to decide if you need to insert or update.

You also have 3 separate stored procedures in your database for each of the actions.

It seems to me that this is poorly structured code, since you would pass the same parameters to your insert / update procedures as you would, and therefore it makes sense to do this all in one place.

Using UPSERT, you have 1 stored procedure and only one connection is required, with one method being called from code. I think this makes a lot better, cleaner code.