@ @ERROR and @ @ROWCOUNT usage

1. @ERROR

If the current statement encounters an error, an error number is returned, otherwise 0 is returned. It is important to note that @error is reset immediately after each statement is executed, so you should check the value after execution of the statement you want to validate or save it in a local variable for future use. For specific analysis please see the code below, at a glance.

Update Bl_consumer set mobile= ' 432142342342342342342342342342342342 ' where uid=1335

PRINT getdate ()--After execution @ @ERROR is 0

PRINT @ @ERROR
IF @ @ERROR = 0

BEGIN

PRINT @ @ERROR

END

-----------------------------------------------------------------

Msg 8152, Level 16, State 14, line 3rd
string or binary data is truncated.
Statement has been terminated.
9 3:33pm
0
0

Analysis: The above update SQL statement causes string or binary data, throws an error message, so the @ @Error after executing the UPDATE statement is 8152, but after executing the first print @Error statement output error message, note that At this point the @ @Error data immediately becomes 0!, which is very important, because @ @Error is reset immediately after each statement executes! Similarly, the value of @ @ERROR after executing if @ @ERROR = 0 statement is still 0, because there is no error in this sentence!

2. @ @ROWCOUNT

Returns the number of rows affected by the previous statement! The same characteristics as @error are reset after each statement is executed, and if you want to save the variable to a local variable in the future. Any statement that does not return will have this variable set to 0! For example, frequently used if statements. Say less nonsense, on the code!

Update Bl_consumer set mobile= ' Zhu Xiaowen ' where uid=1335

PRINT getdate ()--After execution @ @ROWCOUNT

PRINT @ @ROWCOUNT
IF @ @ROWCOUNT = 0

BEGIN

PRINT @ @ROWCOUNT

END

-----------------------------------------------------------------

(2 rows affected)

The number of rows affected is 1

0

The number of rows affected is: 2

Analysis: After executing the UPDATE statement in the above code, the number of rows affected is 2 rows, and then the number of rows affected is saved to the pre-declared local variables, the assignment statement actually affects the number of rows is 1 rows, in the following if statement jumps, this place is the key, This is not the number of rows affected by the UPDATE statement being 1, but rather the assignment of the @ @ROWCOUNT to the local variable into 1. After execution, if @ @ROWCOUNT = 1 after the judgment, the value of the @ @ROWCOUNT is re-assigned to 0!

Summary: The values of the @ @ROWCOUNT and @ @ERROR variables always change after a statement is executed, so we should first save them in local variables as a basis for judgment. They reflect the impact of the last statement immediately after them!

@ @ERROR and @ @ROWCOUNT usage