chapter31

development in a land far far away…

at the moment

History is not what happened. History is what was written down.

*Note that this example is using MSSQL with an Identity set to generate the primary key automatically.

An all too common process developers go through when building database driven web applications is creating logic to INSERT a record when one doesn’t exist, and UPDATE a record when one does exist.

This often leads to conditional logic within your application (inside a Service layer perhaps) and then running one of 2 SQL statements depending on the existance of a primary key value which may be coming from a form or URL.

You would more than likely have separate methods for INSERTING and UPDATING, but for the sake of simplicity these will be together:

That’s it, this approach is a nice way to start examining you DAO’s.

Another way to achieve this is by using a stored procedure (there are many, many positive reasons for using a stored procedure. See here and here for more information).

Using a stored procedure can be cleaner as you just need the one piece of code in your application leaving the rest of the logic to be handled in the database.

The following example is for scenarios where you don’t pass a primary key value, the <cfprocparam> will pass null=”true” to the stored procedure. This is how the SQL will know to run an INSERT statement.

On the other hand if I have a primary key value, the <cfprocparam> will pass the value (with null=”false”) and my stored procedure will run an UPDATE.

You can see that the only change was that in the second example myPrimaryKey actually had a value.

And now for the simple stored procedure. Note I’m setting a default value of null for the @myPrimaryKey parameter, this is how the stored procedure can handle both scenarios. You can of course set default values for all of your parameters.


Related Pages

8 Responses to “Combining insert and update SQL logic”

  1. you do realize that by having a conditional statement inside your stored procedure (SP) you negate the benefits of using a SP since it will be recompiled on every execution.

    This page has more information:
    http://www.databasejournal.com/features/mssql/article.php/1565961

    The proper way is to break down your SP into smaller SPs and having the main SP call them. In your example, you would create 3 SPs. One for the insert, one for the update and the main that would call the insert or update SP. This would give you better performance since the insert and update SPs wouldn’t be recompiled only the main one would.

    tony petruzzi

  2. Another approach is to do an update, run select @@ as recordsUpdated , check the value of recordsupdated, and if it’s 0 , do an insert. This is similar to the Oracle Upsert concept.

    jim collins

  3. Good idea. A couple of notes.
    If you are wrapping this in a CFC function, you could handle what Tony talks about inside the function, calling different SPs inside the function depending on whether a myprimarykey is passed.

    I would recommend that such a function always return the primarykey value because after successful execution, you will always have one.

    For SQL server, you could use the following to return this from your insert:
    IF (@myPrimaryKey Is Null)
    BEGIN
    –no record exists so run an INSERT statement
    INSERT INTO myTable(FirstName, LastName, Email, Age)
    VALUES (@firstName, @lastName, @email, @age)

    SELECT SCOPE_IDENTITY()
    END

    Chip Temm

  4. Oracle has nice feature named Merge aka Upsert that make this a breeze. Here is a url that explain it usage.

    http://www.psoug.org/reference/merge.html

    Thanks

    Qasim Rasheed

  5. Hi Tony,

    I didn’t know this was the case, but reading the link you provided doesn’t hold up for me.

    - Structual changes are not being made
    - Not using WITH RECOMPILE
    - Not using large amounts of INSERT, UPDATE or DELETE

    I haven’t come across anything which states that using conditional logic inside a SP will make it recompile on every execution.

    The following was from MSDN (for MSSQL2000):

    “If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server.”

    Mind you I haven’t used the Profiler on this to try and capture when the SP is recompiling :)

    Maybe that’s my next step.

    Michael Sharman

  6. Hi Chip,

    I see what you’re saying, what I was getting at in the example though was avoiding having that logic in your application code.

    The example I used could easily return SCOPE_IDENTITY from the stored proc as well, thanks for that!

    Michael Sharman

  7. For MySQL users there is the REPLACE INTO command (to replace INSERT INTO statements). This looks for matching rows with the same unique keys and deletes them before adding the new row.

    Andrew McGregor

  8. [...] stuck again. After some more searching I lucked out and found this article. If you look at the second code example you’ll see where he’s using another property of [...]

    Phoenix Web Concepts » Blog Archive » Creating a dynamic stored procedure with SQL Server and ColdFusion

Leave a Reply