title: Missing insert into table in try catch
body: <p>I have a C# app that reads a service bus and execute a stored procedure. It has 5 steps, one to insert this data into a table and then manipulate it (its our ETL). The DB is SQL Server.</p>
  <p>The process is inserting data correctly into <code>STG.Table1</code>, but the second statement that inserts into <code>DWH.Table1</code> sometimes doesn't insert but neither catch the error into the Log nor the C# app. The validated column its an computed column to validate if the json is valid.</p>
  <p>For nearly the 200k records that I have I missed 5k, I'm missing like 3% data in the <code>DWH.Table1</code> table.</p>
  <p>Here is my code. I hope you can help me with this I really appreciated.</p>
  <p>My stored procedure:</p>
  <pre><code>DECLARE @tableid TABLE (id bigint);
  DECLARE @id BIGINT;
  DECLARE @json_id VARCHAR(32);
  
  BEGIN TRANSACTION
  
      INSERT INTO STG.Table1 (c1, c2) 
      OUTPUT inserted.id INTO @tableid 
      VALUES (@v1, @v2);
  
  COMMIT TRANSACTION
  
  BEGIN TRY
  
      SELECT @id = id FROM @tableid
  
      SELECT @json_id = JSON_VALUE(t.json,'$.json_id')
      FROM STG.Table1 t
      INNER JOIN @tableid doc ON t.id = doc.id
      WHERE validated = 'True'
      
      BEGIN TRANSACTION
      
          INSERT INTO DWH.Table1 ([Id], [json_id])
              SELECT  
                  t.id,
                  JSON_VALUE(t.json,'$.json_id') transaction_id
              FROM 
                  STG.Table1 t
              INNER JOIN 
                  @tableid t1 ON t.id = t1.id
              WHERE 
                  validated = 'True'
          
      COMMIT TRANSACTION
  
      SELECT id FROM @tableid
  END TRY
  
  BEGIN CATCH
      DECLARE
          @errorLine INT = ERROR_LINE(),
          @errorProcedure NVARCHAR(128) = ERROR_PROCEDURE(),
          @errorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
          @reportMessage NVARCHAR(4000)
      
      SET @reportMessage = CONCAT('Line ', @errorLine, ' :: ', @errorMessage,' :: json_id = ',@json_id)
  
      INSERT INTO PRC.LOGS (errorProcedure, reportMessage)  
      VALUES (@errorProcedure, @reportMessage)
          
      DELETE FROM STG.Table1
      WHERE id = @id;
      
      THROW
  END CATCH
  </code></pre>