Opinions on putting Insert and Update code into same proc

Question

Opinions on putting Insert and Update code into same proc

Chris Lane-152984

SSChasing Mays

Points: 636
More actions
October 1, 2008 at 9:56 am

#199190

I am looking for opinions on putting Insert and Update code into the same proc.
Personally I think it is sloppy and I fell there should be separate CRUD procs, espcially when the proc is called usp_InsertCustomer and you have no idea that this proc will do an update if the CustomerId already exists instead of the insert.
I am open to changing my opinion though.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I also prefer separate procs. Of course with the new MERGE syntax in 2008 that would probably change. Then the name would be usp_MergeCustomer or something that identifies it. In place where I have done insert/update in one proc, I'd name it usp_InsertUpdateCustomer.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jack Corbett (10/2/2008)
I also prefer separate procs. Of course with the new MERGE syntax in 2008 that would probably change. Then the name would be usp_MergeCustomer or something that identifies it. In place where I have done insert/update in one proc, I'd name it usp_InsertUpdateCustomer.

Hmm...I haven't seen what they have done to MERGE between 2005 and 2008, but I am not a big fan of what's in 2005. While combining these two (and conditional DELETES) into one single statement sound cute, in my opinion it turn the code into some giant mass, that's harder to read, and no more performant (actually it looks to be slower than the separate statements so far). So right now - it just doesn't seem to give much, and takes away a lot of readability.

Regardless - in most cases - CRUD interfaces really need the two operations separated anyway, so I am not sure MERGE would help much.

(This is a REALLY long way to say - I would also agree that I'd usually do separate SP's).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I've seen it done both ways. No best answer, it really depends on whether or not there is a hard and fast rule that says if-exists-then-update/if-not-exists-then-insert. If that is the rule, then putting both functions in one procedure takes it out of the hands of the application which calls the proc. It also gives you one place to put additional functionality that is shared by your inserts or updates.

The downside of this approach is that the calling application gets no feedback about whether or not the action taken was the action intended. That is, the application may intend to create a new row, but instead update an existing (but unexpected) row, rather than returning an error. If the decision to insert or update is situational and varies from application to application, then separate procedures allow more control by the calling app.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 4

It really depends on the situation. We do a lot of combined Update/Insert SPs to import data. We do this as the data we get is often mostly updates with inserts thrown in the mix from one file and from the next it might be all inserts. The maintenance cost of doing this in separate SPs was a nightmare. So I changed it.

Basically I don't check to see if it exists before running the update. I just update what I can, and then insert where it doesn't exist. Since I'm typically working with an import file with 1 - 10 million records and hitting tables with 80-200 million tables performance is very important!

Now, if I was doing onesy twosy stuff where I know ahead of time what needs to happen to the data, I would likely have separate SPs. But for my back-end processing of large batches of data it will almost always be Update/Insert in the same SP.

Gary Johnson
Sr Database Engineer

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

I agree... it depends. If it's CRUD, it should probably be done in separate procs. If it's to merge data from a staging table into a final table, it should probably be in the same proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Lane-152984 SSChasing Mays Points: 636 More actions · Answer 6

I completely agree with the large datasets and importing data then for performance it makes more sense to combine them in the same proc. It shouldn't be all that hard to add in the proper error handling so the caller knows what failed. In my case I was referring to a small single update/insert being done in the same proc which I do not like.

THanks all for the input.

bobhovious SSC Enthusiast Points: 188 More actions · Answer 7

I concur on having in the same proc for mass insert/updates. Separate procs are usually desirable to support single-row transactions through user interfaces.