*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:
<cfif len(form.myPrimaryKey)>
<!--- there is a primary key meaning a record exists, UPDATE --->
<cfquery name="qSetData" datasource="dsn">
UPDATE myTable
SET field1 = '#trim(form.field1)#'
, field2 = '#trim(form.field1)#'
WHERE myPrimaryKey = #form.myPrimaryKey#
</cfquery>
<cfelse>
<!--- there is no primary key meaning a record doesn't exists, INSERT a new row --->
<cfquery name="qCreateData" datasource="dsn">
INSERT INTO myTable (field1, field2)
VALUES ('#trim(form.field1)#', '#trim(form.field1)#')
</cfquery>
</cfif>
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.
<cfscript> //as there is no value here an INSERT statement will be run myPrimaryKey = ""; firstName = "Michael"; lastName = "Sharman"; email = "michael@wherever.com"; age = "31"; </cfscript> <cfstoredproc datasource="dsn" procedure="setUser"> <cfprocparam cfsqltype="CF_SQL_INTEGER" null="#NOT len(trim(myPrimaryKey))#" value="#myPrimaryKey#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#firstName#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#lastName#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#email#" /> <cfprocparam cfsqltype="CF_SQL_INTEGER" null="#age#" value="#age#" /> </cfstoredproc>
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.
<cfscript> //as there is a value here an UPDATE statement will be run myPrimaryKey = "13"; firstName = "Michael"; lastName = "Sharman"; email = "michael@wherever.com"; age = "31"; </cfscript> <cfstoredproc datasource="dsn" procedure="setUser"> <cfprocparam cfsqltype="CF_SQL_INTEGER" null="#NOT len(trim(myPrimaryKey))#" value="#myPrimaryKey#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#firstName#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#lastName#" /> <cfprocparam cfsqltype="CF_SQL_VARCHAR" null="false" value="#email#" /> <cfprocparam cfsqltype="CF_SQL_INTEGER" null="#age#" value="#age#" /> </cfstoredproc>
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.
CREATE PROCEDURE setUser ( @myPrimaryKey int = null ,@firstName varchar(50) ,@lastName varchar(50) ,@email varchar(50) , @age int ) AS 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) END ELSE BEGIN --we have a record, run an UPDATE UPDATE myTable SET firstName = @firstName , lastName = @lastName , email = @email , age = @age WHERE myPrimaryKey = @myPrimaryKey END GO

8 Comments
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.
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.
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
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
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.
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!
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.
superb site site to clear all doubts abt sql