title: Commit tran on @@ROWCOUNT
body: <p>I am trying to wrap a few actions in a transaction so I can determine if I should delete a table in the last step.  Here's what I have so far:</p>
  
  <pre><code>    --select the DB
      use DB1
  
  
      --if the table exists, we want to delete it first
      IF (EXISTS (SELECT * 
                       FROM INFORMATION_SCHEMA.TABLES 
                       WHERE TABLE_SCHEMA = 'dbo' 
                       AND  TABLE_NAME = 'VV'))
      BEGIN
          drop table dbo.VV
      END
  
  BEGIN TRAN  
  
      SELECT field1
            ,field2
            ,field3
  
      INTO dbo.vv
        FROM vvr A
       WHERE A.field1 &lt;&gt; 'GEN'
       AND A.field2 &lt;&gt; 'NO DATA'
       AND A.field3 &lt;&gt; '(BLANK) NO'
  
  PRINT 'ROW1:' +  CAST(@@ROWCOUNT as varchar(11))
  IF @@ROWCOUNT = 0 
          ROLLBACK TRAN 
      ELSE
          COMMIT TRAN
  
  
      UPDATE dbo.vv 
      SET dbo.field1 = vvr.field1
      FROM dbo.vv
  
  PRINT 'ROW2:' +  CAST(@@ROWCOUNT as varchar(11))
  
  IF @@ROWCOUNT = 0 
          ROLLBACK TRAN 
      ELSE
          COMMIT TRAN
  </code></pre>
  
  <p>when I run this without the transaction statements, it runs just fine, so I know the SQL works but when I add the transaction statements, it fails telling me the table VV doesn't exist.  When I do a select on VV, it is definitely gone.  </p>
  
  <p>Once I get the above to run fine I will add one more statement at the end to drop table vvr but I haven't got that far yet.</p>