@ @Error use a simple summary

Using the @ @Error often used to determine whether the previous statement is successful, a summary of this, may be some inaccurate, welcome to point out.

1.1 Introduction

@@ represents system global variables in SQL SERVER

(1) Returns the error number of the last Transact-SQL statement executed, or 0 if no error is executed.

(2) If the error is one of the errors in the Sys.messages catalog view, the @ @ERROR will contain the value in the SYS.MESSAGES.MESSAGE_ID column that represents the error. You can view text information related to the @ @ERROR error number in sys.messages.

(3) Since the @ @ERROR is cleared and reset after each statement is executed, it should be viewed immediately after the statement is validated, or saved to a local variable for later viewing.

1.2 Examples and use

(1) parse execution SQL error, execution of subsequent scripts

Execute statement:

UPDATE tborder SET orderno = ' 201605010008 ' WHERE orderno = ' 201605010001 '

--Execution of a FOREIGN KEY constraint error in the previous step, and proceed to next step (547 is a constraint error)

Print @ @ERROR-Output error number

UPDATE tborder SET orderamount = ' ABC ' WHERE orderno = ' 201605010001 '

--A type exception occurred while performing the previous step, and do not perform the following steps

PRINT @ @ERROR--not output

description : After the 1th SQL error, the following code is still executed, the @ @Error error number is output, and the 2nd SQL error immediately terminates subsequent executions.

Indicates that after the execution of SQL error, some errors will terminate the subsequent execution directly, and some can continue to execute subsequent scripts after an error occurs.

(2) when the query succeeds, the system variable @ @Error becomes 0

Execute statement:

DELETE from tborder WHERE orderno = ' 201605010001 '

print ' Delete: ' + CAST (@ @ERROR as VARCHAR (20))--Output

SELECT 1

print ' Select: ' + CAST (@ @ERROR as VARCHAR (20))--Output select:0

description : After executing SQL successfully, the @ @Error parameter was set to 0

(3) catch error through try catch, output @ @Error

A) or the above delete operation, resulting in a foreign key constraint problem

Execute statement:

BEGIN TRY

DELETE from tborder WHERE orderno = ' 201605010001 '

PRINT ' DELETE ' + CAST (@ @ERROR as VARCHAR (20))--No output

END TRY

BEGIN CATCH

PRINT ' CATCH ' + CAST (@ @ERROR as VARCHAR (20))

PRINT Error_message ()

PRINT error_severity ()

PRINT Error_state ()

END CATCH

GO

Output Result:

b) or the above update operation, resulting in an error, no subsequent code execution

Execute statement:

BEGIN TRY

UPDATE tborder SET orderamount = ' ABC ' WHERE orderno = ' 201605010001 '

PRINT ' UPDATE ' + CAST (@ @ERROR as VARCHAR (20))

END TRY

BEGIN CATCH

PRINT ' CATCH ' + CAST (@ @ERROR as VARCHAR (20))

PRINT Error_message ()

PRINT error_severity ()

PRINT Error_state ()

END CATCH

GO

Output Result:

description : After executing SQL error in try, directly caught by catch, subsequent code in try is terminated, execute code in catch directly

(4) test If a thing's operation produces an error and rolls back

A) directly add things

Execute script:

BEGIN TRAN

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 50,200,10)

--Execution error

DELETE from tborder WHERE orderno = ' 201605010001 '

PRINT ' DELETE: ' + CAST (@ @ERROR as VARCHAR (20))

COMMIT TRAN

Execution results (also query Order Details):

description : After performing an error, the previous insert operation data did not roll back, indicating that adding things directly does not roll back the data before the error

b) Add @ @Error to determine if execution is successful

Execute script:

BEGIN TRAN

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 50,200,10)

IF @ @ERROR <> 0

BEGIN

ROLLBACK TRAN

RETURN

END

--Execution error

DELETE from tborder WHERE orderno = ' 201605010001 '

--update tborder SET orderamount = ' ABC ' WHERE orderno = ' 201605010001 '--replace the delete operation with an update operation and the thing will be rolled back after the exception is generated

IF @ @ERROR <> 0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRAN

Execution results (also query Order Details):

Description : added @ @Error Judgment, the previous data was rolled back when the error was executed. When you replace a delete operation in a statement with an exception that performs a masked update operation, things are also rolled back

(5) test error by setting xact_abort whether the whole thing is rolled back

Note : When SET xact_abort is on, the entire transaction terminates and rolls back if the Transact-SQL statement produces a run-time error.

When SET Xact_abort is OFF, sometimes only the Transact-SQL statement that produces the error is rolled back, and the transaction continues processing. If the error is severe, the entire transaction may be rolled back even if SET xact_abort is OFF. OFF is the default setting.

A) Xact_abort is OFF, only the rollback that performed the error

Execute script:

SET Xact_abort OFF

BEGIN TRAN

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 50,200,10)

--Execution error

DELETE from tborder WHERE orderno = ' 201605010001 '

PRINT ' DELETE: ' + CAST (@ @ERROR as VARCHAR (20))

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 51,10,100)

COMMIT TRAN

Execution results (query details at the same time):

b) Xact_abort is on, forcing the whole thing to roll back

Execute script:

SET Xact_abort on

BEGIN TRAN

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 50,200,10)

--Execution error

DELETE from tborder WHERE orderno = ' 201605010001 '

PRINT ' DELETE: ' + CAST (@ @ERROR as VARCHAR (20))

COMMIT TRAN

SET Xact_abort OFF

Execution results (query details at the same time):

Note : Set Xact_abort to OFF (default is off), only the statement that executes the error is rolled back, the other does not roll back the data, and when the Xact_abort is on, the execution of the SQL generates an error, forcing the whole thing to roll back, and not executing the subsequent code

(6) catch errors through Try catch, show things execution errors, and roll back

Execute script:

BEGIN TRAN

BEGIN TRY

--Perform normal

INSERT into Dbo.tborderdetail (OrderNo, ProductID, Quantity, Price)

VALUES (' 201605010001 ', 50,200,10)

--Execution error

DELETE from tborder WHERE orderno = ' 201605010001 '

--update tborder SET orderamount = ' ABC ' WHERE orderno = ' 201605010001 '

COMMIT TRAN

END TRY

BEGIN CATCH

PRINT ' Execute error ' + CAST (@ @ERROR as VARCHAR (20))

PRINT Error_message ()

PRINT error_severity ()

PRINT Error_state ()

ROLLBACK TRAN--must increase rollback, otherwise things will hang dead where

END CATCH

Execution results (also query Order Details):

1.3 Summary Notes

With the use of the above demonstration and results, the following 3 ways to ensure data integrity

(1) If the statement executes successfully by the @ @Error, does the thing need to be rolled back (see 1.2 (4))

(2) Set Xact_abort to ON, forcing the whole thing to be rolled back when error occurs (see 1.2 (5))

(3) The execution exception is captured by a try catch and the thing is rolled back (see 1.2 (6))

1.4 References

Microsoft website explained

Https://msdn.microsoft.com/zh-cn/library/ms188790.aspx

Some errors and workarounds not caught by try Catch

http://blog.csdn.net/kk185800961/article/details/40043415

Transaction Execution Tracking Analysis

http://blog.csdn.net/zhaowenzhong/article/details/16342843

1.5 Related Accessories

Related SQL scripts

@ @Error Use a simple summary