Use of XACT_STATE - Sql Server 2005

XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.


XACT_STATE returns the following values.


1 : The current request has an active user transaction. The request can           perform any actions, including writing data and committing the transaction.


0 : There is no active user transaction for the current request.


-1: The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

begin try

CREATE TABLE #TBL_RECORDS([Name] VARCHAR(500),[Membership Number] VARCHAR(50))

BULK INSERT #TBL_RECORDS
FROM 'C:\Test.csv'
WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FIRSTROW=1)
end try

begin catch
IF XACT_STATE() = -1
begin print 'Error'
EndELSE IF XACT_STATE() = 0
begin print 'OK'
Endelsebegin print 'ErrorNew'
Endend catch

Suggestions are always welcome.


Thanks.