--------------------------------------------------------------------------------
--  File        :   Demo 10_Sample fault tolerance code in T-SQL,2005+.sql
--
--  Purpose     :   Typically, fault tolerance should be done in the data 
--                  access layer (application) code.  General development 
--                  languages (C#, C++, Java, Visual Basic, etc.) are better 
--                  suited to this type of error handling.  For an example, 
--                  see the following blog post:
--                      "Fault Retry provider and sample" <http://blogs.msdn.com/bonniefe/archive/2008/11/17/fault-retry-provider-and-sample.aspx>
--
--                  With that said, it is now possible to do so in T-SQL, 
--                  beginning with SQL Server 2005, using the new TRY...CATCH 
--                  construct.
--
--                  The following example is taken directly from _Inside 
--                  Microsoft SQL Server 2005: Query Tuning and Optimization_ 
--                  by Kalen Delaney (found on pages 365 and 366).
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   3.0006.0090.0053 (2010-11-17)
--------------------------------------------------------------------------------

DECLARE @Tries tinyint, @Error int;
SET @Tries = 1;
WHILE @Tries <= 3
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        --  <code goes here>
        IF XACT_STATE() = 1 COMMIT;
        BREAK;
    END TRY
    BEGIN CATCH
        SET @Error = ERROR_NUMBER();
        IF @Error = 1205
        BEGIN
            IF XACT_STATE() = -1 ROLLBACK;
        END
        SET @Tries = @Tries + 1;
        CONTINUE;
    END CATCH;
END;
