title: How to commit and rollback transaction in sql server?
body: <p>I have a huge script for creating tables and porting data from one server. So this sceipt basically has - </p>
  
  <ol>
  <li>Create statements for tables.</li>
  <li>Insert for porting the data to these newly created tables.</li>
  <li>Create statements for stored procedures. </li>
  </ol>
  
  <p>So I have this code but it does not work basically @@ERROR is always zero I think..</p>
  
  <pre><code>BEGIN TRANSACTION
  --CREATES
  --INSERTS
  --STORED PROCEDURES CREATES
      -- ON ERROR ROLLBACK ELSE COMMIT THE TRANSACTION
      IF @@ERROR != 0
          BEGIN
  
              PRINT @@ERROR
                        PRINT 'ERROR IN SCRIPT'
              ROLLBACK TRANSACTION
              RETURN
          END
      ELSE
      BEGIN
          COMMIT TRANSACTION
          PRINT 'COMMITTED SUCCESSFULLY'
      END
      GO
  </code></pre>
  
  <p>Can anyone help me write a transaction which will basically rollback on error and commit if everything is fine..Can I use <a href="http://msdn.microsoft.com/en-us/library/ms178592.aspx" rel="noreferrer">RaiseError</a> somehow here..</p>