SQL @ error @ rowcount returns an exception and number of affected rows

1. @ Error

 

If an error occurs in the current statement, the error code is returned. Otherwise, 0 is returned. Note that @ error is reset immediately after each statement is executed. Therefore, check the value after the statement to be verified is executed or save it to a local variable for future use. For detailed analysis, see the following code.

Update af_carinfo set carinfoid = 19 Where oilcost = 13 -- after execution @ error is 547

Print @ error -- after execution @ error is 0

If @ error = 0

Begin

Print @ Error

End

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

Message 547, level 16, status 0, 1st rows

The update statement conflicts with the reference constraint "fk_af_milee_reference_af_carin. This conflict occurs in the Database "lymit_printerp", table "DBO. af_mileenrol", column 'carinfoid '.

The statement has been terminated.

547

0

Analysis: The above updated SQL statement causes a conflict between the primary and Foreign keys and throws an error message. Therefore, after executing the update statement, the @ error value is 547, however, after executing the first print @ error statement to output the error message, note that the data of @ error is changed to 0 immediately !, This is very important because @ error is reset immediately after each statement is executed! Similarly, after the if @ error = 0 statement is executed, the value of @ error is still 0, because there is no error in this sentence!

2. @ rowcount

 

Returns the number of affected rows in the previous statement! Similar to @ error, each statement is reset after execution. If it is used in the future, you need to save the variable to a local variable. Any statement not returned sets this variable to 0! For example, if statements are frequently used. Let alone code!

Declare @ rowcountvar int

Update af_caroil set oiltype = 'gasoline 5' -- after execution @ rowcount is 2

Set @ rowcountvar = @ rowcount -- after execution @ rowcount is 1

If @ rowcount = 1 -- after execution @ rowcount is 0

Begin

Print 'Affected rows are 1'

Print @ rowcount

End

If @ rowcountvar <> 0

Begin

Print 'Affected rows: '+ STR (@ rowcountvar)

End

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

 

(2 rows affected)

The number of affected rows is 1.

0

Affected rows: 2

Analysis: In the above Code, after executing the update statement, the number of affected rows is two, and then the affected rows are saved to the locally declared variable, in fact, the number of rows affected by the value assignment statement is one line, and the next if statement jumps. This is the key. This is not because the number of rows affected by the update statement is 1, instead, the process of assigning @ rowcount to a local variable is changed to 1. After execution, the value of @ rowcount = 1 is assigned to 0 again!

Summary: The values of the @ rowcount and @ error variables always change after a statement is executed, therefore, we should first save them in local variables as the basis for judgment. What they reflect is the impact of the previous statement on them!