title: Multiple batches in a SQL transaction
body: <p>I want to create several stored procedures (or user-defined functions) within a transaction. <code>CREATE PROCEDURE</code> statement must be the only statement in the batch, so I have to use following template:</p>
  
  <pre class="lang-sql prettyprint-override"><code>BEGIN TRANSACTION MyTransaction
  
      USE MyDatabase
      GO
  
      CREATE PROCEDURE A
      AS
          -- The body of procedure A
      GO
  
      CREATE PROCEDURE B
      AS
          -- The body of procedure B
      GO
  
      CREATE PROCEDURE C
      AS
          -- The body of procedure C
      GO
  
  COMMIT TRANSACTION MyTransaction
  </code></pre>
  
  <p>The problem appears if an error occurs within one of the batches. For example, if error occurs while procedure <strong>B</strong> is creating, <code>MyTransaction</code> with procedures <strong>A</strong> and <strong>B</strong> will be rolled back. But the script will continue to run. So, the procedure <strong>C</strong> will be created.</p>
  
  <p>Also it's not possible to skip the statement using <code>GOTO</code> and <code>@@error</code> counter checking, because it cannot go to a label outside the batch in which <code>GOTO</code> is placed.</p>
  
  <p>How to prevent any database changes if an error occurs?</p>