I frequently come across situations where I need a way to insert a key/value pair into a lookup table or update it if it already exists. So I figured I’d write a stored procedure that would do the following things for me:

♦ Accept the lookup key and value as input arguments and return the unique id of the new (or existing) key that was just inserted (or updated) as an output argument.
♦ Correctly deal with race conditions where two separate entities (processes, threads, etc…) are trying to insert or update the same lookup key/value pair and accomplish this without using a transaction.

For this discussion, we’ll assume that our lookup table contains a list of performance counters. The (reasonably) well commented T-SQL code below should be self-explanatory:

CREATE PROCEDURE [dbo].[usp_PutPerfCounter]
(
   @PerfCounterName varchar(50),
   @PerfCounterValue int output
) AS BEGIN
   DECLARE @nRowCount int
   DECLARE @nErr int
   DECLARE @nErrSeverity int
   DECLARE @strErrMsg nvarchar(4000)
   DECLARE @nIns int
   DECLARE @nErrState int
   DECLARE @nExisting int

   SELECT @nErr = 0, @nIns = 0, @nErrSeverity = 0, @nErrState = 0

   -- Make sure that @PerfCounterName argument is valid
   IF @PerfCounterName IS NULL OR LEN(@PerfCounterName) = 0
   BEGIN
      RAISERROR('Perf counter name must be provided.',16,1)
   END
   ELSE
   BEGIN
      SELECT @nExisting = COUNT(DISTINCT PerfCounterName)
      FROM PerfCounter WHERE PerfCounterName = @PerfCounterName

      -- Check to see if the there is already an existing
      -- entry with the same key.
      IF(@nExisting = 0)
      BEGIN
         -- The try/catch feature is new in SQL Server 2005 and
         -- here we use it to squash exceptions generated due to
         -- a potential unique constraint violation error because
         -- our intention is to handle this particular error within
         -- the stored proc without passing it on to the application
         -- layer.

         BEGIN TRY
            INSERT INTO
               [dbo].[PerfCounter](PerfCounterName, PerfCounterValue)
            VALUES
               (@PerfCounterName, @PerfCounterValue )
            SELECT @nIns = 1
         END TRY
         BEGIN CATCH
            SELECT @nErr = @@ERROR
            SELECT @strErrMsg = ERROR_MESSAGE()
            SELECT @nErrSeverity = ERROR_SEVERITY()
            SELECT @nErrState = ERROR_STATE()

            -- A value of 2627 for @nErr means that
            -- between the time we ruled out a pre-existing
            -- entry with the same key and our attempt to
            -- insert (what we thought) was a new entry,

            -- another entity has already successfully inserted
            -- a new entry with the same key that we're trying to insert.
            -- We'll let the first successful insert stick and just
            -- return that value in the input/output parameter
            -- (@PerfCounterValue).

            IF @nErr = 2627 -- 2627 stands for unique constraint violation
            BEGIN
               SELECT @PerfCounterValue = PerfCounterValue
               FROM [dbo].[PerfCounter]
               WHERE PerfCounterName = @PerfCounterName
            END
            ELSE IF( @nErr <> 0 ) -- Some other occurred...
            BEGIN
               RAISERROR(@strErrMsg,@nErrSeverity,@nErrState)
            END
         END CATCH
      END
   END

   -- Update the PerfCounterValue - assuming we haven't
   -- already inserted another value.
   IF( @nIns = 0)
   BEGIN
      UPDATE [dbo].[PerfCounter]
         SET PerfCounterValue = @PerfCounterValue
         WHERE PerfCounterName = @PerfCounterName
   END
END

NOTE: As mentioned in the comments embedded in the T-SQL code, this technique relies on the new try/catch feature available only in SQL Server 2005 (and beyond, I assume).

NOTE: In this example, I chose to go with a policy of letting the value inserted by the first successful process stick in the event of a race condition. This could easily be changed if the application calls for the value associated with the last successful insert to stick.