Web Biz

A conditional Insert is often used when inserting a record, and you want to make sure that the record doesn’t already exist. There are several ways to accomplish this task. First, you can rely on Primary Key or Unique constraints to ensure data integrity, which are best-practice and should be implemented. You can also add some logic to your SQL statements to prevent duplicate records.

In MySQL, you can provide a WHERE clause to your INSERT statement to check if the record already exists:

1
2

INSERT INTO tbl (col1, col2) VALUES ('1', '2')
WHERE NOT EXISTS (SELECT * FROM tbl WHERE col1='1' AND col2='2')

In SQL Server, you cannot provide a WHERE clause to your INSERT statment. However, there are some additional methods to accomplish the same thing. I started out with something like:

1
2

IF NOT EXISTS (SELECT * FROM tbl WHERE col1='1 AND col2='2')
INSERT INTO tbl (col1, col2) VALUES ('1', '2')

This appears to work at first glance. After doing a little research, I realized that its certainly possible for duplicate entries with the above SQL statment. The first query can return, indicating that the record doesn’t exist, another session could then update the table before my next line runs, which then inserts a duplicate entry. To avoid this, we first wrap the statments in a transaction, and then specify an update and hold lock.

A hold lock directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. By using an update lock, we block the execution of statements that also require a hold lock (like an update statementor queries using the updlock hint), but not others reading the table.

During the execution of the select statement we would like to block execution the same select statement on other threads. A handy hint we can use is the UPDLOCK hint, it means that we will acquire update locks instead of shared locks. This does not block other readers, but will block statements which need to also hold update locks (like update statements or queries using the UPDLOCK hint). Both locks will be released when the transaction is committed. Here is what my conditional insert statement looks like now:

1
2
3
4
5
6

BEGIN TRAN
 
  IF NOT EXISTS (SELECT * FROM tbl WITH(updlock,holdlock) WHERE col1='1 AND col2='2')
  INSERT INTO tbl (col1, col2) VALUES ('1', '2')
 
COMMIT TRAN

Rolling concept into SP

A great concept extension of this, is to utilize this in your stored procedure that provides the data layer for your component’s save() or commit() method.

Out CFC might look something like:

User.cfc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<cfcomponent extends="Base">
 
  <cffunction name="save">
 
    <cfstoredproc datasource="#dsn#" procedure="sp_user_save">
 
      <cfprocresult="Identity">
 
      <cfprocparam type="in" dbvarname="UserID" value="#variables.userid#" cfsqltype="cf_sql_integer" null="#not isNumeric(variables.userid)#">
 
      <cfprocparam type="in" dbvarname="Firstname" value="#variables.firstname#" cfsqltype="cf_sql_varchar">
 
      <cfprocparam type="in" dbvarname="Lastname" value="#variables.firstname#" cfsqltype="cf_sql_varchar">
 
    </cfstoredproc>
 
  </cffunction>
 
</cfomponent>

Your procedure might look like:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE PROCEDURE sp_user_save
  @userid int = NULL, @firstname varchar(100) = NULL, @lastname varchar(200) = NULL
 
AS
BEGIN
  BEGIN TRAN
    IF EXISTS (SELECT * FROM tbl WITH(updlock,holdlock) WHERE col1='1 AND col2='2')
      UPDATE users SET firstname=@firstname, lastname=@lastname WHERE UserID=@userid
    ELSE
      INSERT INTO users (firstname,lastname) VALUES (@firstname, @lastname)
  COMMIT TRAN
  SELECT @@IDENTITY AS Identity
END

This could certainly provide some great benefits; including the caching of the query plan in SQL Server by using a stored proc, ease of code in CF, and ensures data integrity at the database level.