Transaction + try...catch Joint use in stored procedures

When a transaction is used in a stored procedure, if there is no Try...catch statement, when set XACT_ABORT on, if an error occurs, all SQL operations are automatically rolled back after the batch statement is completed.

When set Xact_abort off, if an error occurs, after the batch statement is completed, the system executes all statements that do not have an error, and the statement that the error occurs will not be executed.

When a transaction is used in a stored procedure, if a Try...catch statement block exists, a rollback action is required manually in the Catch statement block when an error is caught, otherwise the system sends an error message to the client.

If the set XACT_ABORT on at the beginning of the stored procedure, the system will place the current transaction in an uncommitted state when there is an error, and the Xact_state () will be set to 1, where the transaction can only be rollback and cannot be committed (commit) Operation, we can determine whether a transaction is in an xact_state state in a catch statement block, or if there is a rollback operation.

If the set Xact_abort off at the beginning of the stored procedure, then when there is an error, the system does not speak Xact_state () to 1, then we cannot judge in the catch block whether a rollback is needed, according to the function value. But we can judge according to the @ @Trancount global variable, if the value of @ @Trancount is judged to be greater than 0 in the catch block, and there are uncommitted transactions, then there are uncommitted transactions. The transaction should be rollback, but in some cases it may not be accurate to judge.

The recommended approach is to use set XACT_ABORT on, and then judge the value of Xact_state () in a catch to determine whether a rollback operation is required.

CREATE TABLE #temp (AA varchar ())

set xact_abort on
BEGIN TRY
    -Generate divide-by-zero error.
    BEGIN TRAN
    INSERT INTO #temp select ' 00000 '
    select 1/0;
    Commit Tran End
    
TRY
BEGIN CATCH
  if xact_state () =-1
  begin
  Rollback tran
  PRINT
	' ErrorNumber: ' +cast (Error_number () as VARCHAR) + char (+) +char (a) +
	' errormessage: ' +error_message () + char ( +char (a) +
	' errorline: ' +cast (Error_line () as VARCHAR)
        --error_number () as ErrorNumber,
        -- Error_severity () as ErrorSeverity,
        --error_state () as ErrorState,
        --error_procedure () as Errorprocedure,
        --error_line () as ErrorLine,
        --error_message () as errormessage;        
  End-end

CATCH;

SELECT * FROM #temp
drop table #temp

/* (1 rows affected)

-----------

(0 rows affected)

errornumber:8134< C31/>errormessage: Encountered a divide-by-zero error.
errorline:9
aa
--------------------

(0 lines affected)
* *