Split sprocs for Insert / Update or one Set sproc?
So I ran into an instance where the stored procedure that was handling updates to a specific table was not doing anything because the record did not exist [duh].
So what I decided to do was change the stored procedure to:
UPDATE Table
SET col1 = @var1
WHERE Type = @type AND UserId = @userId
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Table
---etc....
END
So this obviously solved the problem and I renamed the sproc from UpdateTable to SetTable.
This is a good idea, or should I be doing something at the application level where if the update returns 0 rows then call the insert routine.
source to share
I prefer the "upsert" you showed because it allows me to stay much further away from the persistence logic. In an application, I just want to work with my objects and "if I must" ask them to save / save. When updating and inserting separately, you have to think about it in the domain model. But the biggest benefit of persistence ignorance is that you can focus on real business issues rather than infrastructure issues.
This design also helped me migrate to ORM earlier this year because I didn't have a ton of "updates" / "inserts" but instead just "save"
source to share
It is good if this fits the expected application.
If the business intent was only to update an existing record, OR if the application was expecting the row to already exist and the stored pass silently hides the fact that it didn't, it could break other logic. What if another function / process deleted the row for some reason? What if there was information in columns that were not updated in a row? Insertion will possibly change the values of these columns unexpectedly.
Not to say that it is. Only someone familiar with the application can determine if the change is normal.
source to share
Depends.
In some places in the application it makes sense to do an upsert because you cannot know or care if the current saved state should already contain an entry. However, elsewhere you need to know for sure, and the pre-existence of a record in an insert or a missing record to update should be a mistake.
In any case, when you decide to do an "upsert", you must be careful, though due to their inherent race conditions. Two separate connections might try to update as shown by @@ ROWCOUNT to 0 and then both try to insert, resulting in a primary key violation (if you're lucky) or worse in an inconsistent database state (duplicate record with different information). Obtaining such operations is quite difficult. You must either ensure that the UPDATE is locked in state (i.e., no other transaction can insert) or be locked to hit the PK violation on the insert and handle it accordingly. I prefer the later option as the race conditions window is usually quite small and handling the exception is easier than preventing the insertion match.
One way to consider is using the new SQL Server 2008 MERGE , it is designed to handle cases like this (and finally catching up with SQL Server with other vendors, both Oracle and MySQL have been offering this functionality for years ...).
source to share