问最佳实践尝试/捕获抛出和XACT_ABORT sql server

EN

SET XACT_ABORT on

SET NOCOUNT ON

BEGIN TRY

'DO STUFF'
End Try
BEGIN CATCH

SET @return_msg = 'procedure name FAILED'

  + '. Step ' + CONVERT(VARCHAR,ISNULL(@v_step,0))

  + '. Error Num: ' + CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),0))

  + '. Error Msg: ' + ERROR_MESSAGE()

  SELECT @return_msg

-- Test XACT_STATE for 0, 1, or -1.  
    -- If 1, the transaction is committable.  
    -- If -1, the transaction is uncommittable and should   
    --     be rolled back.  
    -- XACT_STATE = 0 means there is no transaction and  
    --     a commit or rollback operation would generate an error.  

    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT 'The transaction is in an uncommittable state.' +  
              ' Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  

    -- Test whether the transaction is active and valid.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT 'The transaction is committable.' +   
              ' Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  

END CATCH

  RETURN 0