Post reply

Opinions on putting Insert and Update code into same proc

  • 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.

  • October 2, 2008 at 7:22 pm

    #880253

    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

  • October 2, 2008 at 10:21 pm

    #880281

    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?

  • October 3, 2008 at 4:10 pm

    #880726

    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

  • October 3, 2008 at 8:03 pm

    #880778

    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

  • October 3, 2008 at 8:55 pm

    #880788

    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)
    Intro to Tally Tables and Functions

  • October 5, 2008 at 11:06 pm

    #880959

    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.

  • October 6, 2008 at 3:22 pm

    #881356

    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.

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

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