Когда дело доходит до обработки ошибок в хранимых процедурах SQL Server, большинство людей полагается на переменную @@ERROR для захвата и обработки ошибок. Однако, что если вам нужно захватить фактическое сообщение об ошибке, вызванное SQL Server внутри хранимой процедуры? Возможно ли получить и зарегистрировать это сообщение об ошибке для ваших собственных систем?

В этой статье мы рассмотрим решение этой проблемы, используя журнал ошибок SQL Server и хранимую процедуру sp_altermessage.

Проблема

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

Некоторые SQL-специалисты предложили использовать DBCC OUTPUTBUFFER или процедуры sp_Oa с динамическим SQL и SQL-DMO для захвата сообщений об ошибках. Однако эти подходы могут быть ненадежными или чрезмерно сложными.

Решение

При изучении таблицы sysmessages я наткнулся на хранимую процедуру sp_altermessage. Эта процедура позволяет администратору базы данных настроить регистрацию ошибок в журнале ошибок SQL Server. Это дало мне идею: настроить регистрацию наиболее вероятных ошибок и затем прочитать их из журнала ошибок.

Например, я настроил ошибки 515, 547, 2601, 2627 и 3902 для самозаписи с помощью sp_altermessage. Это означает, что каждый раз, когда происходит нарушение ограничения (например, первичного ключа, внешнего ключа, not null или уникального ограничения), ошибка регистрируется в журнале ошибок SQL.

Вот пример того, как вы можете захватить и получить описание ошибки с использованием этого подхода:

Set xact_abort off  -- требуется !
Declare @intError INT
Declare @VcDescr VARCHAR(1000)
Insert testtable (PkId ) select null  -- ожидаемая ошибка, как указано выше
Select @intError =  @@ERROR
If @intError <> 0
 Begin
        Exec GetErrorStringSP @intError, @VcDescr OUTPUT
        Print @VcDescr
 End

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

Соображения

Хотя этот подход предоставляет решение для захвата описаний ошибок в хранимых процедурах, есть несколько недостатков, о которых следует знать:

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

Несмотря на эти соображения, этот подход доказал свою эффективность в соответствии с требованиями проекта и может быть полезен для SQL-специалистов, сталкивающихся с подобными проблемами.

Надеюсь, вам понравилась эта статья, которая поможет вам понять, как захватывать описания ошибок в хранимых процедурах SQL Server. Не стесняйтесь загрузить предоставленный код и адаптировать его под свои потребности.