Имеем запрос, меняющий данные на MS SQL Server. Задача: отловить возникшие при выполнении запроса ошибки и откатить все уже внесенные внутри запроса изменения к состоянию на момент его запуска. Помогут нам в этом нелегком деле транзакции и блок TRY-CATCH. Когда вообще возникает необходимость? Типичный пример из учебника, последовательное удаление двух строк из разных таблиц, имеющих один и тот же код:

DELETE FROM SOME_TABLE1 WHERE SOME_ID = SOME_VALUE
DELETE FROM SOME_TABLE2 WHERE SOME_ID = SOME_VALUE

Если первый оператор выполнится, а второй нет, нас поразит самое страшное проклятье любого разработчика - рассогласование данных в базе. Само собой возможно множество других ситуаций, когда частичное выполнение запроса приведет к большим проблемам.

Транзакция - это последовательность операций, которая должна или полностью выполниться успешно или не выполнится вообще. В Transact Sql она начинается командой BEGIN TRANSACTION и продолжается до выполнения команды отката ROLLBACK TRANSACTION или подтверждения COMMIT TRANSACTION. Само собой команду отката надо выполнять в случае ошибок. Как их отследить? Теоретически есть переменная @@ERROR, содержащая код ошибки или 0 в случае успешного выполнения, но на практике такой код писать нельзя

IF @@ERROR <> 0 ROLLBACK

так как переменная @@ERRROR переопределяется после каждой SQL-команды. То есть если первый DELETE вылетит с ошибкой, в переменную запишется код ошибки, если второй оператор выполнится успешно, то в переменную запишется ноль и мы ничтоже сумняшеся , подтвердим изменения.

И здесь нам придет на помощь блок TRY-CATCH, хорошо знакомый програмиирующим на C# (и не только) товарищам. Делает он хорошо знакомую вещь, отлавливает любую ошибку в блоке TRY и перебрасывает управление вместе с ошибкой в блок CATCH. В итоге мы получим вот такой код:

-- переменные для переброса данных об ошибке из CATCH в вызывающий код после отката изменений
DECLARE @errorMessage nvarchar(4000), @errorSeverity int
BEGIN TRY
  BEGIN TRANSACTION
    -- удаляем таблицу, копируем новую сервера, прописываем права
    DROP SOME_TABLE 
    -- если с другим сервером возникнет проблема, нужен откат
    SELECT  SOME_FIELD1, SOME_FIELD2, SOME_FIELD3 INTO SOME_TABLE FROM  OTHER_SERVER.OTHER_DATDABSE.dbo.OTHER_TABLE
    GRANT SELECT ON OBJECT::SOME_TABLE TO some_role; 
  -- если дошли до этой строки, все успешно, подтверждаем изменения
  COMMIT
END TRY
BEGIN CATCH
  -- COMMIT должен был уменьшить эту переменную до нуля, откатываем изменения 
  IF @@TRANCOUNT > 0 ROLLBACK
  -- выбрасываем новую ошибку в информацией из ошибки, пойманной оператором CATCH 
  SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY()
  RAISERROR(@errorMessage, @errorSeverity, 1)
END CATCH

Более подробно:

1. TRY...CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error
2. TRY...CATCH (Transact-SQL)
3. BEGIN TRANSACTION (Transact-SQL)