title: Parts of stored procedure just not running
body: <p>I have a stored procedure which runs a series of inserts but in some cases it seems like one of the inserts isn't running but the rest of the code following it runs fine.</p>
  
  <pre><code>DECLARE @ID1 int
  DECLARE @ID2 int
  
  -- This works
  INSERT INTO table1 (field1,field2)
  VALUES('test', 1)
  SELECT @ID1 = SCOPE_IDENTITY() -- This picks up the correct ID
  
  -- This sometimes doesn't seem to run or an error of some sort happens infrequently
  INSERT INTO table2 (field1, field2, field3, field4, field5)
  VALUES(1,2,3,4,@ID1)
  
  -- This always runs, but if the previous insert doesn't run inserts the previous ID into @ID2
  SELECT @ID2 = SCOPE_IDENTITY()
  
  -- Inserts the ID from the previous step and therefore is sometimes wrong
  INSERT INTO table3 (field1,field2)
  VALUES ('testing', @ID2)
  </code></pre>
  
  <p>This is a redacted sample of the SP. Why would this sometimes happen? I would have thought if there was an issue with the <code>INSERT</code> the rest of the procedure would bomb out? But the same code works, ran by the same person, most of the time. I have checked to see if there is any table locking going on at the same time of the issues, but there doesn't seem to be anything I can spot.</p>
  
  <p>What is the best way to find out what the issue is? Or how should I restructure the above code to negate the possible impact?</p>