Tags

, , , ,


Found a bit of interesting MS SQL Server / T-SQL behavior on @@ROWCOUNT function.

As we know, @@ROWCOUNT function is used to get the number of affected row by the last statement (or number of result row if the last statement is a SELECT statement).

However, if the statement issued inside a TRY..CATCH block, and the @@ROWCOUNT function called outside that block, then the @@ROWCOUNT will always return 0. Of course there are many ways to get the correct row count, but I’ll choose the easiest one here.

Lets have a look on these SQL commands:

BEGIN TRY
SELECT 1
END TRY

BEGIN CATCH
SELECT 2
END CATCH

SELECT @@ROWCOUNT

When these commands executed, the last SELECT statement will returns 0, not 1. This is because the database engine will consider the END TRY as the last statement, and this statement is not affecting any rows. So to get the correct row count, we can call the @@ROWCOUNT function inside the block and pass it to other variable if we want to access it outside the block.

Here are the above commands with the correction:

DECLARE @ROWS INT

BEGIN TRY
SELECT 1
SET @ROWS = @@ROWCOUNT
END TRY

BEGIN CATCH
SELECT 2
END CATCH

SELECT @ROW

And the last statement will return 1.
I hope this little information can be of any help. Thanks for reading, leave a comment :)

About these ads