title: Detect if Stored Proc is being called within a transaction
body: <p>Ok - so the answer is probably 'everything is within a transaction in SQLServer'. But here's my problem.</p>
  
  <p>I have a stored procedure which returns data based on one input parameter. This is being called from an external service which I believe is Java-based.</p>
  
  <p>I have a log-table to see how long each hit to this proc is taking. Kind of like this (simplified)...</p>
  
  <pre><code>TABLE log (ID INT PRIMARY KEY IDENTITY(1,1), Param VARCHAR(10), In DATETIME, Out DATETIME)
  
  PROC troublesome
  (@param VARCHAR(10))
  BEGIN
      --log the 'in' time and keep the ID
      DECLARE @logid INT
      INSERT log (In,Param) VALUES (GET_DATE(),@param)
      SET @logid = SCOPE_IDENTITY()
  
      SELECT &lt;some stuff from another table based on @param&gt;
  
      --set the 'out' time
      UPDATE log SET Out = GET_DATE() WHERE ID = @logid
  
  END
  </code></pre>
  
  <p>So far so easily-criticised by SQL fascists.</p>
  
  <p>Anyway - When I call this eg <code>troublesome 'SOMEPARAM'</code> - I get the result of the <code>SELECT</code> back and in the log table is a nice new row with the <code>SOMEPARAM</code> and the in and out timestamps.</p>
  
  <p>Now - I watch this table, and even though no rows are going in - if <em>I</em> am to generate a row, I will see that the ID has skipped many places. I guess this is being caused by the external client code hitting it. They are getting the result of the <code>SELECT</code> - but I am not getting their log data.</p>
  
  <p>This suggests to me they are wrapping their client call in a <code>TRAN</code> and rolling it back. Which is <em>one</em> thing that would cause this behaviour. I want to know...</p>
  
  <ul>
  <li>If there is a way I can FORCE the write of the log even if it is contained within a transaction over which I have no control and which is subsequently rolled back (seems unlikely)</li>
  <li>If there is a way I can determine from within this proc if it is executing within a transaction (and perhaps raise an error)</li>
  <li>If there are other possible explanations for the ID skipping (and it's skipping <em>alot</em> like 1000 places in an hour. So I'm <em>sure</em> it's caused by the client code - as they are reporting successfully retrieving the results of the <code>SELECT</code> also.)</li>
  </ul>
  
  <p>Thanks!</p>