A small misunderstanding about @ @error in SQL Server

In the original: a small misunderstanding about @ @error in SQL Server

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

if (@ @error<>0)     ROLLBACK TRANSACTION T Else    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:

SETNOCOUNT on;SETXact_abort on;--executing a Transact-SQL statement produces a run-time error, the entire transaction terminates and rolls backBEGIN TRANSACTIONTUPDATETestSETA='has been updated'WHEREA='not updated'RAISERROR('Sorry, you don't have permission! ', -,1)

SELECT GETDATE()if(@ @error<>0)    ROLLBACK TRANSACTIONTElse    COMMIT TRANSACTIONT

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:

SETNOCOUNT on;SETXact_abort on;--executing a Transact-SQL statement produces a run-time error, the entire transaction terminates and rolls backBEGINTRYBEGIN TRANSACTIONTUPDATETestSETA='has been updated'    WHEREA='not updated'    RAISERROR('Sorry, you don't have permission! ', -,1)    SELECT GETDATE()        COMMIT TRANSACTIONTENDTRYBEGINCATCHDECLARE @msg nvarchar( -)=Error_message ()--The error message to be caught exists in the variable @msg    RAISERROR(@msg, -,1)--you can throw it here (like this ...)    ROLLBACK TRANSACTIONT--Wrong back to the rolling businessENDCatch

A small misunderstanding about @ @error in SQL Server