title: SQL query that'll rollback if any statements fails
body: <p>I would like to write a SQL script that executes multiple individual SQL statements; if any of those statements fail, I'd like to rollback the entire transaction.  So, something like:</p>
  
  <pre><code>BEGIN TRANSACTION
  
  insert into TestTable values (1)
  insert into TestTable values (2)
  insert into TestTabe values (3)
  
  --if any of the statements fail
  ROLLBACK
  --else
  COMMIT
  </code></pre>
  
  <p>This is for MS SQL 2008.  Is there anything I can do to accomplish this?  Perhaps some kind of exception handling?</p>
  
  <p>I realize in my example I could inspect the TestTable for these values and determine if the statements failed that way.  But in reality my SQL will be much more complex and I'd rather abstract myself from knowing what the SQL was doing.</p>