title: Using TRY / CATCH to perform INSERT / UPDATE
body: <p>I have this pattern in a number of stored procedures </p>
  
  <pre><code>-- Table1
  [id] [int] IDENTITY(1,1) NOT NULL
  [data] [varchar](512) NULL
  [count] INT NULL
  
  -- 'data' is unique, with a unique index on 'data' in 'Table1'
  BEGIN TRY 
      INSERT INTO Table1 (data, count) SELECT @data,1;
  END TRY
  BEGIN CATCH
      UPDATE Table1 SET count = count + 1 WHERE data = @data;
  END CATCH
  </code></pre>
  
  <p>I've been slammed <a href="https://stackoverflow.com/questions/30710135/abstracting-out-a-query-to-a-stored-procedure-makes-it-run-very-slowly">before</a> for using this pattern</p>
  
  <blockquote>
    <p>You should never have exception "catching" in your normal logic flow. (Thus why it is called an "exception"..it should be exceptional (rare). Put a exists check around your INSERT. "if not exists (select null from Data where data = @data) begin /* insert here */ END</p>
  </blockquote>
  
  <p>However, I can't see a way around it in this instance. Consider the following alternative approaches. </p>
  
  <pre><code>INSERT INTO Table1 (data,count) 
  SELECT @data,1 WHERE NOT EXISTS 
      (SELECT 1 FROM Table1 WHERE data = @data)
  </code></pre>
  
  <p>If I do this, it means every insert is unique, but I can't 'catch' an update condition. </p>
  
  <pre><code>DECLARE @id INT;  
  SET @id = (SELECT id FROM Table1 WHERE data = @data)
  
  IF(@id IS NULL)
      INSERT INTO Table1 (data, count) SELECT @data,1;
  ELSE 
      UPDATE Table1 SET count = count + 1 WHERE data = @data;
  </code></pre>
  
  <p>If I do this, I have a race condition between the check and the insert, so I could have duplicates inserted. </p>
  
  <pre><code>BEGIN TRANSACTION
     DECLARE @id INT;  
     SET @id = (SELECT id FROM Table1 WHERE data = @data)
  
     IF(@id IS NULL)
         INSERT INTO Table1 (data, count) SELECT @data,1;
     ELSE 
         UPDATE Table1 SET count = count + 1 WHERE data = @data;
  END TRANSACTION
  </code></pre>
  
  <p>If I wrap this in a <code>TRANSACTION</code> it adds more overhead. I know <code>TRY/CATCH</code> also brings overhead but I <em>think</em> <code>TRANSACTION</code> adds more - anyone know?. </p>
  
  <p>People keep telling me that using <code>TRY/CATCH</code> in normal app logic is <em>BAD</em>, but won't tell me <em>why</em></p>
  
  <p>Note: I'm running SQL Server 2005 on at least one box, so I <strong>can't use <code>MERGE</code></strong></p>