Еще бывают ситуации когда ошибка в процедуре приводит к завершению активного батча, или даже обрывает текущее соединение. Т.е. проверять @@ERROR негде и XACT_ABORT тоже не срабатывает. В первом варианте ошибку можно перехватить так:
DECLARE @rc int
SET @rc = sp_executesql 'Ваш подозрительный код'
IF @rc<>0
BEGIN
-- Обработка ошибки
END

Со вторым вариантом сложнее, но тоже не совсем безнадежно.
Например можно исполнить его с помощью xp_cmdshell и osql
Конечно это крайний случай. Лучше не допускать даже возможности
возникновения подобных ошибок в коде.

Ну чего же сразу наезжать
Я же не говорю, что надо sp_executesql лепить в любом контексте.
Все зависит от ситуации.
Например в таком случае до проверки @@ERROR дело вообще не дойдет:

select * from tbl
if @@ERROR<>0
BEGIN
print 'No such table ...'
END