Для длинного списка операций последовательность действий примерно такая
BEGIN TRY
BEGIN TRANSACTION
DO SOMETHING...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TranCount >0
ROLLBACK TRANSACTION
END CATCH
Без подтверждения или отката транзакций никаких откатов происходить не будет. Отработают инструкции до возникновения ошибки и останутся в базе.