[abark]

точнее для моей версии SQL 2008 будет так:

DECLARE @RetryCount smallint
SET @RetryCount = 5
WHILE 1=1
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
        ...
        COMMIT TRANSACTION
        BREAK
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        IF ERROR_NUMBER() in (1204 /*SqlOutOfLocks*/, 1205 /*SqlDeadlockVictim*/, 1222 /*SqlLockRequestTimeout*/) and @RetryCount > 0
        begin
            SET @RetryCount = @RetryCount - 1
            WAITFOR DELAY '00:00:02'
        end
        ELSE  --    ,    
        begin
            --THROW ; -- SQL 2012
            -- SQL 2008: CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block
            DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200);
            -- Assign variables to error-handling functions that capture information for RAISERROR.
            SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), 
                   @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
            -- Build the message string that will contain original error information.
            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();
            -- Raise an error: msg_str parameter of RAISERROR will contain the original error information.
            RAISERROR (@ErrorMessage, @ErrorSeverity, 1,               
                       @ErrorNumber,    -- parameter: original error number.
                       @ErrorSeverity,  -- parameter: original error severity.
                       @ErrorState,     -- parameter: original error state.
                       @ErrorProcedure, -- parameter: original error procedure name.
                       @ErrorLine       -- parameter: original error line number.
                      );
            break;
        end
    END CATCH;
END