@@ROWCOUNT AND TRANSACTION

I was testing some checking techniques to add to my SPs while retreiving data or inserting a series of reocords to a series of tables.

Check this testing small piece of code:

begin transaction t1select * from roles where rolename = 'student'if @.@.rowcount = 0goto cleanupselect * from usersif @.@.rowcount = 0goto cleanupcommit transaction t1goto endupcleanup:rollback transaction t1SET ROWCOUNT 0endup:print @.@.rowcount

On one of the testing cases: I made the first query return 1 record and the second returned 5 records, so we ended up at the label "endup", I printed out the RowCount it was 0, does the commit transaction reset that variable?

In addition, are there smart and effective techniques to check against validity of the query statements inside the SP, for example, I can use @.@.ERROR and @.@.ROWCOUNT in a transaction based SP to know if I proceed with sub queries, are there more stuff like that?

Regards

Bilal,

Actually I don't know if commit transaction resets the@.@.rowcount field, but from my experience the only way to use the @.@.rowcount andthe @.@.error fields, is to declare local variables and immediately after DML statementscopy the @.@.rowcount and @.@.error fields to the local variable, for example:

Declare @.xRowCount int

Declare @.xError int

Select ……

Select @.xRowCount = @.@.rowcount, @.xError = @.@.Error

Following this method you are sure that the values don't get"messed up". I always insert this "copystatement" after select, insert, update and delete statements.

BTW:

Why do you have the SET Rowcount 0 after the rollback statement(The set rowcount 0 has nothing to do with the value in the @.@.rowcount)?And why do you use transactions in this example (I mean as long as you justhave select statements and none update, insert or delete statements?).

|||

Hello:

Thanks for the reply.

I usually use transactions when I have update, insert or delete and off ocurse multiple statements need to be executed. I used it here to show something, wierd for me at least. The first statement returned @.@.ROWCOUNT = 1, the second 5, and the final result (ROWSCOUNT value) was 0 and that made me think of the effect of the transaction on RowCount.

Thanks for the hints.

Regards

|||Hi.

You are right. The commit transaction reset the rowcount to 0 (at least according to the SqlServer 2005 documentation).

The documentation stats that:
Statementssuch as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Mark the words "Such as", meaning you can't be sure about the other statements. In other words, never trust the @.@.rowcount except right after a select, insert, update or delete statement.

I agree that is't a bit wired.