Menu
I like to use Upsert stored procedures that update records if they exist or insert them if they don't. Without them, I would need to first find out if the record exists, and then have two separate stored procedures that I would call based on the result.
I never really thought about the issue before today when I was creating a stored procedure called UpdateOrDeleteRow. As soon as I found myself including "Or" in the name, my SRP spider sense kicked in, and I realized that the upserts are basically the same thing.
Is this a violation of SRP? If so, is it acceptable? If not, what should I do?
I realize that the SRP is an OOP principle, and T-SQL is not an OOP language, but the basis for the principle seems like it should apply here as well.
831
You cannot violate the Single Responsibility Principle because it is just an esthetic criterion, not a rule of Nature.
For example, if we have a class that we change a lot, and for different reasons, then this class should be broken down into more classes, each handling a single concern.
The Single Responsibility Principle (SRP) is the concept that any single object in object-oriented programing (OOP) should be made for one specific function. SRP is part of SOLID programming principles put forth by Robert Martin. Traditionally, code that is in keeping with SRP has a single function per class.
The correct answers are: Objects should have a singular purpose. Generally, a class wouldn't make calls to external methods. A class should only have one reason to change.
There is another principle, which I like even more, than SRP - DRY. So, if you call this sequence in one place, you can think about single responsibility. But when you repeating same sequence of actions several times, DRY makes me to remove duplication.
BTW Just come to my mind, that you can avoid OR in procedure/method name. UpdateOrInsert operation has very good name Save. I think it does not breaks SRP.
56
Personally I don't believe that this principle applies completely in SQL Server. Stored procedures don't always perform just one action (and I think the notion that a stored procedure is equivalent to a class is flawed). I don't think it makes sense to split every single statement in a stored procedure into its own stored procedure. You can get absolutely ridiculous with this.
There is a balance of course, as you can be ridiculous the other way. You don't want a stored procedure with 18 different ways to specify parameters so that it can do 540 different things based on the combinations.
For an UPSERT I would still suggest that a single stored procedure is fine for this. If you want to feel better about it serving a single purpose, change your update/insert into a single MERGE. :-) That said, and in all seriousness, be very careful with MERGE.
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
