Separate sprocs for Insert/Update or one Set sproc?

Question

So I came across an instance where a stored procedure which handled the Updates on a specific table was doing nothing because the record didn't 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.

Is this a good idea, or should I do something at the application level where if the update returns 0 rows affected, call the insert procedure.

Answer 1

I prefer the "upsert" that you have shown because it allows me to stay that much further away from persistence related logic. In the application I just want to work with my objects and "if I must" ask them to save / persist. With update and insert separately you are forced to think about this in the domain model. But the biggest advantage to persistence ignorance is that you can focus on real business problems instead of infrastructure concerns.

This design also helped me migrate to an ORM earlier this year because I didn't have a ton of "update" / "insert" calls, but instead a simple "save"

Answer 2

I like to have one for each. I makes the sproc perform a specific task instead of two, making it easier to maintain and extend.