title: sql server: inserting into a table within a transaction for logging error occurance?
body: <p>I have a SP (Stored procedure) which is contained of some T-SQL statements.....</p>
  
  <p>All of T-sql statements are in a transaction block and by occuring any error, I rollback every thing.</p>
  
  <p>like this:</p>
  
  <pre><code>BEGIN TRANSACTION
  .....
  .....
  IF @X=1
  BEGIN
      declare cu cursor for select col1,col2 from Table1 where Id=@Y
      open  cu
      fetch next from cuinto @A, @B
      while  @@Fetch_Status = 0
      BEGIN
           .....
          ......
          IF @@ERROR &lt;&gt;0
          BEGIN
              ROLLBACK TRANSACTION
              RETURN
          END
  END
  .....
  .....
  </code></pre>
  
  <p>The Sp does not run properly and I can't find what the resean of it is.....
  I think it's a good idea to log every operation within sp by inserting some data into a table 
  My Question is:</p>
  
  <p>Because it uses a transaction, every insertion will be rolled back.....</p>
  
  <p>What's your opinion? Is there any other way?</p>
  
  <p>Thank you </p>