*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.
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
April 11th, 2007
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
April 11th, 2007
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
April 11th, 2007
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
April 11th, 2007
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
April 11th, 2007
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
April 11th, 2007
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
July 25th, 2007
[...] 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
August 17th, 2007