Неправильно.
Для достижения желаемого необходимо в процедуре до начала транзакции сказать:
SET XACT_ABORT ON
В этом случае при любой ошибке транзакция будет сама откачена и исполнение прервано.

SET XACT_ABORT ON
begin transaction
commit transaction

работает. Можешь проверить в ISQLW. А ставить SET XACT_ABORT ON можно в любом месте до объявлении транзакции. Маленькая деталь: выполнение оператора RAISERROR внутри транзакции не будет считаться ошибкой и не произведет действий по прерыванию батча и отката транзакции.

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

Пример такой:

set xact_abort ON
begin tran
update Banks set IdCityType=56 where Id=245731 -- FK нарушается
update Banks set IdCityType=1 where Id=245732 -- FK не нарушается
commit
go
select IdCityType from Banks where Id in (245731, 245732)

результат:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Banks_CityTypes'. The conflict occurred in database 'OfficeAppDB', table 'CityTypes', column 'Id'.
IdCityType
----------
NULL
NULL

(2 row(s) affected)

Далее:

set xact_abort OFF
begin tran
update Banks set IdCityType=56 where Id=245731 -- FK нарушается
update Banks set IdCityType=1 where Id=245732 -- FK не нарушается
commit
go
select IdCityType from Banks where Id in (245731, 245732)

результат:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Banks_CityTypes'. The conflict occurred in database 'OfficeAppDB', table 'CityTypes', column 'Id'.
The statement has been terminated.

(1 row(s) affected)

IdCityType
----------
NULL
1

(2 row(s) affected)


Хотите верьте, хотите - нет, но работает, как именно я говорил.