What is the function of this code?

Xact_State() is used to determine whether there are active user transactions are not.
It may hold 0,1,or -1 value.

O- no active transactions.
1-The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
-1 -There is a current active user transaction, but it has some errors


In the above code, they are using try and catch blocks. All updates/inserts are done inside try block. So whenever error occurs and it comes to catch, then we need to do rollback for entire tran. Before doing rollback, here they are just checking whether there is an active transaction. If there is an active transaction, then it ll rollback and raise error

For eg:

I have a table temp
Column GroupId (int)
COlumn ReleaseDate (datetime)

Now i have a rows like this.

101 1900-01-02 00:00:00.000
101 1900-01-02 00:00:00.000
102 2012-07-31 00:00:00.000
102 2012-08-02 00:00:00.000
102 2012-08-05 00:00:00.000
103 2012-08-01 00:00:00.000
103 2012-08-06 00:00:00.000

Am trying to update release date column with 'test' which is incorrect. It will throw error.
I have two different blocks of code, one is with transaction, another without tranasaction.

1.

Declare @mErrMsg varchar(Max)
Begin Try
Begin Tran
 
update temp
set Releasedate='test'
where groupId=101

Commit Tran
End Try
Begin Catch
    If (XACT_STATE()) <> 0
    Begin
        Rollback Tran
        print  'rollback'
    End
    ELse
		print 'not inside XACT_STATE condition'
    Select @mErrMsg = Error_Message()
    Raiserror(@mErrMsg,16,1)
End Catch

The output will be
rollback
Msg 50000, Level 16, State 1, Line 21
Conversion failed when converting date and/or time from character string.

Because there is a begin tran, so there is an active tran and value of XACT_STATE<>0

2. In the scenario I didnt use begin tran

Declare @mErrMsg varchar(Max)
Begin Try

 
update temp
set Releasedate='test'
where groupId=101

Commit Tran
End Try
Begin Catch
    If (XACT_STATE()) <> 0
    Begin
        Rollback Tran
        print  'rollback'
    End
    ELse
		print 'not inside XACT_STATE condition'
    Select @mErrMsg = Error_Message()
    Raiserror(@mErrMsg,16,1)
End Catch

The output will be
not inside XACT_STATE condition
Msg 50000, Level 16, State 1, Line 22
Conversion failed when converting date and/or time from character string.


Because there is no begin tran, so there is no active tran and value of XACT_STATE will be equal to 0

If some exception will occur while updating your data then it'll check to XACT_STATE(Transact-SQL) [^]. If XACT_STATE is not returning 0(There is no active user transaction for the current request), then the transaction will be rolled back else it will not be.
And finally the error will be raised.


--Amit