Commit tran on @@ROWCOUNT

Question

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:

    --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 <> 'GEN'
     AND A.field2 <> 'NO DATA'
     AND A.field3 <> '(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

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.

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.

Answer 1

If you want to perform multiple actions based on the number of rows that a single statement affected, then you need to capture that value into your own variable immediately:

DECLARE @rc int
SELECT field1
      ,field2
      ,field3

INTO dbo.vv
  FROM vvr A
 WHERE A.field1 <> 'GEN'
 AND A.field2 <> 'NO DATA'
 AND A.field3 <> '(BLANK) NO'

 SET @rc = @@ROWCOUNT
PRINT 'ROW1:' +  CAST(@rc as varchar(11))
IF @rc = 0 
    ROLLBACK TRAN 
ELSE
    COMMIT TRAN

Even simple statements like PRINTs cause @@ROWCOUNT to be assigned a new value (in this case, 0)