title: Proper way to set an explicit transaction in a stored procedure
body: <p>I created a stored procedure that has 9 inserts in summarized tables and the last statement is a delete.</p>
  
  <p>This procedure takes around 9 minutes to execute for each given date, and one thing that I noticed is that if an error occurs and the first 3 inserts executed, it keeps the inserted data. To handle this, I created a begin try and begin transaction and a test that I did was to start the procedure using SSMS and after it started, I cancelled the command but the transaction was kept. How can I avoid this?</p>
  
  <p>the procedure body is very simple... something like:</p>
  
  <pre><code>insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  insert into...
  delete from....
  </code></pre>
  
  <p>thanks</p>