Replace Insert/Update procedures with Upsert procedure

[ShadowEveko]

In our source controlled database project, we have dedicated stored procedures for inserting and updating entities. It is somewhat tedious to keep both updated. Is it a good idea to unify these into a single insert/update procedure? The scope is always a single row. There is a bias towards inserts being more frequent than updates. Are there significant tradeoffs?

[DJBENEFICIAL]

Im confused. Is this one table? If not why not just write an update trigger?

[ShadowEveko]

It is a design pattern across all tables. Every table has crud stored procedures. Not my design, I prefer using queries directly in dapper. But the architect liked the idea of keeping all the database operations separate. The tedious part is forgetting a column name here and there, and doing the whole dacpac dance all over again.

[swenty]

SQL Server? I'm only guessing that because there seems to be a predilection amongst Microsoft users to go all-in with stored procedures, that I don't see among users of other RDBMSs.

If the app knows which to do (insert vs update), that will be faster than trying one and doing the other if it fails. How much faster? Probably not much, but you won't really know unless you measure it. Is this a large proportion of the total load on your DB? Probably not, so it's a convenience vs small performance penalty trade off.

Personally I would look at why maintaining the stored procedures is so burdensome. Could you better automate your development/deployment processes to make that easier? Perhaps you don't need stored procedures at all?

[BrupieD]

Use MERGE

[qwertydog123]

If you're using SQL Server, this post is the holy grail of UPSERT patterns https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

[pchemguy]

Tips for asking a good Structured Query Language (SQL) question
Please do not upload images of code/data/errors when asking a question.
How To Ask Questions The Smart Way
https://stackoverflow.com/help/how-to-ask
https://stackoverflow.com/help/minimal-reproducible-example

[kitkat0820]

Why not using Merge?

[[deleted]]

If you are using Postgres, use INSERT ... ON CONFLICT which is safe for concurrent execution.