To be honest, I don't think it makes any difference which way it is done. Unless you are working with really big data the time taken is irrelevant these days. I was just offering an alternative, my code generator (written in the 90s) does all this work for me
Never underestimate the power of human stupidity
Here it comes...
I got a personal info table - containing id, name, user, password, address, email and phone - with 19,245,718 records. The table has PK and indexes...
Run an insert/update of 108,382 records of which 32,190 where duplicated (same id).
With your method it took an average of 26.77 ms/record.
With the @@ROWCOUNT approach it took an average of 13.07 ms/record.
Both test run on the same machine with local SQL after restart.
The computer is a bit old one (one I can spare) with 4x2.93 CPU and 8GB memory, but I believe that the differences are real...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
The most important advantage of the MERGE statement is that the table is read and processed in one pass only.
So if you insert/update only one record there's no real win. It's when you insert/update from a query you'll get the real performance gain.
MERGE looks like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It is always better to put all the database code on the database side. An equivalent application code for the above query will make atleast three database calls as opposed to one call when the IF is on the database side.
There could be a race condition with you query and the possibility of a row being inserted by another user between the IF and the INSERT, so you should actually handle the exception and take appropriate action (either let the user know that someone else has already inserted it or do an update). The possibility is even higher in case of application code because of network latency between the server and the client.
And your UPDATE statement is missing the WHERE clause and it would end up updating all the rows in the table
I would think this is more efficient than putting the if...else in the application,
And this matters because your application currently has an efficiency problem?
move a lot of code to SQL Server as stored procedures
I use stored procedures for the same reason I have a database layer in an application - to make the layers more distinct and hopefully insuring that access happens in a controlled and reasonable way.
Some business logic which might have performance concerns, can be done must more efficiently in the database server than a client application. There are of course still ways to badly mess this up.
Other than that one problem with using conditional structures in stored procedures is that programmers who do not understand how to really use SQL can end up creating structured programs (ifs, whiles, etc). Of course if one uses stored procedures then if all else fails one can hire a real DBA to redo the stored procs.