title: SQL Try Catch in stored procedure - am I doing it right?
body: <p>I have a stored procedure that performs a number of inserts, each insert will return a <code>SCOPE_IDENTITY()</code> primary key value which is then used in as subsequent insert's foreign key.</p>
  
  <p>To prevent a situation where an insert is performed without a FK because the previous insert has failed, I've wrapped the inserts in try-catch blocks like this:</p>
  
  <pre><code>DECLARE @error INT;
  
  BEGIN TRY
      insert statement here...
      SET @newId = SCOPE_IDENTITY();
  END TRY
  BEGIN CATCH
      -- assign error value to 
      SET @error = @@ERROR;
  END CATCH
  </code></pre>
  
  <p>Then the next insert checks the error var before continuing:</p>
  
  <pre><code>IF @error = 0
  BEGIN
      BEGIN TRY
          next insert statement here using `@newId` from previous qry...
          SET @nextId = SCOPE_IDENTITY();
      END TRY
      BEGIN CATCH
          // assign error value to 
          SET @error = @@ERROR;
      END CATCH
  END
  </code></pre>
  
  <p>And so on. Is this a suitable application of <code>Try-catch</code> or is it overkill?</p>