CODEWITHSUNDEEP
sqltransactions
Baub
Baub

Reputation: 751

Commit tran on @@ROWCOUNT

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.

Upvotes: 8

Views: 14899

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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)

Upvotes: 13

Baub
Baub

Reputation: 751

The PRINT statements I have in there are just for development, I plan to remove them once this works all the way through. So I took those out and the SELECT INTO and UPDATE statements worked fine but none of the DROP statements worked.

The DROP table statement at the top of my code and the one I added was this:

IF @@ROWCOUNT > 0 
BEGIN
    drop table dbo.VVR
END

IF @@ROWCOUNT > 0 COMMIT TRAN

If I run the DROP TABLE statement at the top and then run the whole procedure, I don't get any type of errors, just updates the table as I expect then stops.

Upvotes: 0

user172839
user172839

Reputation: 1075

PRINT 'ROW1:' + CAST(@@ROWCOUNT as varchar(11))

This line resets the @@ROWCOUNT. If you inserted 50 records into the table, the print statement would return 50, but then when you reference @@ROWCOUNT in your next line, the value will return 0, so therefore the table will never exist since you perform a rollback operation

This then causes, the next line (UPDATE statement) to always fail.

Upvotes: 2

Related Questions