none
In my opinion, Only controlling xact_state(), is not enough for transaction management, test the code below. Do you agree with me?

    Question

  • There is sample code below ; In the code, despite the error , xact_state() returns 1. Because of this, to rollback transaction  i am controlling  whether xact_state() is 0 or not. But should i control whether xact_state() is -1 or not

    And you can see the severty of error. It is 16.

    sample code;

    USE [test]
    GO
    /****** Object:  StoredProcedure [dbo].[TryCatchTranYon]    Script Date: 25.9.2013 10:34:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    ALTER procedure [dbo].[TryCatchTranYon]
    (
     @yas1 int,
     @isim1 varchar(100),
      @yas2 int,
     @isim2 varchar(100),
      @yas3 int,
     @isim3 varchar(100)
    )
    as
    begin
    ----------------------------------------------------------------------------
    if exists (select * from sys.tables where type='U' and name='testtable' )
    
    drop table testtable;
    
    create table testtable
    
    (
    yas tinyint,
    isim varchar(50)
    
    
    )
    
    --------------------------------------------------------------------------
    declare 
    @hata varchar(500),
    @severty int, 
     @success int
    set @success=0
    
    ---------------------------------raiserror sample---------------------
    
    if (@yas1<10 or @yas2<10  or @yas3<10  )
    
    begin
    
    raiserror('Age parameters must be bigger than 10 !',11,1)
    return
    
    end 
    
    ------------------------------------------------------------
    
    begin try
    	
    begin transaction 
    
    
    insert into testtable (yas,isim) values (@yas1,@isim1)
    
    
    insert into testtable (yas,isim) values (@yas2,@isim2)
    
    
    insert into testtable (yas,isim) values (@yas3,@isim3)
    
    
    commit transaction
    
    end try
    
    begin catch
    
    
    
    
    
    
    
    select @hata=ERROR_MESSAGE(),@severty=ERROR_SEVERITY()
    
    
    
    set @success=-1
    Raiserror('%s This error is handled. ',11,1,@hata)
    
    
    
         select @@TRANCOUNT trancount,XACT_STATE() xactstate,@severty severty
    
        --IF @@TRANCOUNT > 0
        --    ROLLBACK TRANSACTION;
        
    	if XACT_STATE()<>0
    	begin
    	    ROLLBACK TRANSACTION;
    
        end 
    	
    
    
    end catch
    
    return @success 
    
    end





    Wednesday, September 25, 2013 11:51 AM

Answers

  •   Thanks your reply , i understood my wrong. I was confused because of the Microsoft link that describe xact_state()

    function,

    http://technet.microsoft.com/tr-tr/library/ms189797.aspx  .

    In this link , transaction controls whether xact_state() is -1 to rollback. What  I skipped is that , before the transaction, xact_abort was set to on. Now i learned that , if you do not set xact_abort to on, it is meanless to use  xact_state() function . Because value of this function does not change for every error when xact_abort is set to off.

    Thursday, September 26, 2013 7:46 AM

All replies

  • If your goal is only to rollback or not within the CATCH block, you can simply check @@TRANCOUNT.  The reason to check XACT_ABORT() is if you need to conditionally COMMIT.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, September 25, 2013 11:58 AM
  •   Thanks your reply , i understood my wrong. I was confused because of the Microsoft link that describe xact_state()

    function,

    http://technet.microsoft.com/tr-tr/library/ms189797.aspx  .

    In this link , transaction controls whether xact_state() is -1 to rollback. What  I skipped is that , before the transaction, xact_abort was set to on. Now i learned that , if you do not set xact_abort to on, it is meanless to use  xact_state() function . Because value of this function does not change for every error when xact_abort is set to off.

    Thursday, September 26, 2013 7:46 AM