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 
