A small misunderstanding about @ @error in SQL Server

In SQL Server, I often see some predecessors write this:

if (@ @error <>0)    ROLLBACK TRANSACTION telse    COMMIT TRANSACTION T

At first, I saw someone else write it, and I took it for granted that it was just a counter, and whenever an error was detected, the @ @error value of +1, but for that matter, the cup has ...

In fact, it is not a counter, it is a dynamic value, dynamically identifies the result of the last SQL command execution, or 0 if successful, and identifies the error code if it is unsuccessful. So, like the above writing is inappropriate, for example, as follows:

SET NOCOUNT on; SET xact_abort on;  --Executing a Transact-SQL statement produces a run-time error, the entire transaction terminates and rolls back the begin TRANSACTION tupdate testset a= ' updated ' WHERE a= ' not updated ' RAISERROR (' Sorry, you don't have permission! ', 16, 1)

SELECT GETDATE () if (@ @error <>0)    ROLLBACK TRANSACTION telse    COMMIT TRANSACTION T

Analysis:

According to my previous understanding, "RAISERROR (' Sorry, you don't have permission!") ', 16, 1) ' This throws an error, and the whole transaction should be rolled back, but it's not rolling back!! So what's the reason? Originally, the problem is "select GETDATE ()" This sentence above! Because the value of @ @error is not 0 (as if it were 5000) when executing the RAISERROR statement, the @ @error value becomes 0 when executed to the next sentence "Select GETDATE ()"! Therefore, the following if statement naturally does not catch any errors ...

Countermeasures:

Now that we have found the cause, the solution is natural. With a Try ... The catch syntax is right, with the following statement:

SET NOCOUNT on; SET xact_abort on;  --Executing a Transact-SQL statement produces a run-time error, the entire transaction terminates and rolls back the begin TRY    begin TRANSACTION T update    Test    SET a= ' updated '    WHERE a= ' Not updated '    RAISERROR (' Sorry, you don't have permission! ', 16,1)    SELECT GETDATE ()        COMMIT TRANSACTION TEND trybegin CATCH    DECLARE @msg nvarchar (+) =error_message ()    --The error message that will be caught exists in the variable @msg                   RAISERROR (@msg, 16,1)    --here to throw (as if it were such a child ...)    ROLLBACK TRANSACTION T--wrong rollback end CATCH

A small misunderstanding about @ @error in SQL Server