MS SQL 2011 — Обработка ошибок

Это слово может использоваться в сочетании с управляющей конструкцией Try…Catch и позволяет послать уведомление о возникновении ошибки времени исполнения. Когда возникает исключение, программа ищет ближайший по иерархии вверх блок Catch который может обработать исключение. Используя это выражение внутри блока Catch можно изменить вывод ошибки. Более того, теперь вызывать исключение можно произвольно в любом месте скрипта.

Далее рассмотрим различные способы поимки исключении, которые предоставляет SQL Server начиная с версии 2000 и до версии 2011, с указанием плюсов и минусов.

Для всех рассматриваемых случаев будет использоваться таблица tbl_ExceptionTest.

Для того, чтобы не протыкивать дизайнер мышью, можно выполнить следующий скрипт для создания искомой таблицы (сгенерировано автоматически).

Далее будем пытаться добавить в таблицу несколько записей и при внесении неподходящих данных в колонку Phone Number генерировать исключения.

Обработка ошибок в SQL Server 2000 (Sphinx)

Использование глобальной переменной @@ERROR

Возвращаясь во времена использования SQL Server 2000, вспоминаем что использование переменной @@Error было на тот момент самым прогрессивным и эффективным способом обработки ошибок. Данная переменная отвечала за возврат целочисленного значения ошибки, которое произошло в последнем выполненном выражении. Значение ошибки могло быть как положительным, так и отрицательным, лишь 0 указывал на успешность выполнения операции. Значение переменной менялось после каждого выполненного выражения.

Посмотрим на использование @@Error в действии.

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

Выполнение данного скрипта приведет к появлению ошибки, как показано ниже

Msg 515, Level 16, State 2, Line 26 Cannot insert the value NULL into column ‘Phone Number’, table ‘tempdb. dbo.#tblExceptionTest_____000000000023’; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 16, State 1, Line 43 Attempt to insert null value in [Phone Number] is not allowed

Естественно, что вся транзакция откатится назад и ничего не будет внесено в таблицу.

Недостатки подхода с использованием @@Error

Использование глобальной переменной @@TRANCOUNT

Эта переменная возвращает количество транзакций выполняющихся в момент обращения к переменной. Из описания уже понятно, что она постоянна примерно в той же мере, что и @@ERROR, т. е. постоянно меняется во время исполнения транзакций. Это опять подводит нас к тому, чтобы использовать локальные переменные для хранения значений в интересующий момент времени.

Каждый вызов BEGIN TRANSACTION увеличивает значение @@TRANCOUNT на 1 и каждый вызов COMMIT TRANSACTION уменьшает ее значение на 1. ROLLBACK TRANSACTION не изменяет значения @@TRANCOUNT. Записи считаются внесенными только когда значение @@TRANCOUNT достигнет 0.

Рассмотрим использование @@TRANCOUNT на следующем примере.

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

Для получения дополнительной информации по @@TRANCOUNT обратитесь на MSDN.

Использование глобальной переменной @@ROWCOUNT

Данная переменная возвращает количество измененных строк в результате выполнения запроса/команды.

Поведение такое же, как и у предыдущих двух, так что сохраняем промежуточные результаты в локальную переменную для последующего анализа.

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

Обработка ошибок в SQL Server 2005/2008 (Yukon/Katmai)

После вывода на рынок SQL Server 2005 и развития его идей в SQL Server 2008 у разработчиков на TSql появился новый блок Try…Catch. Теперь стало возможно перехватывать исключения без потери транзакционного контекста.

Пример на использование блока Try … Catch.

В примере больше не используется вспомогательных переменных для определения ошибки выполнения скрипта по косвенным признакам.

После запуска скрипта получим сообщение следующего вида:

Msg 50000, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed

Как вы уже наверно заметили, на этот раз вывелось только то, что было задано в сообщении об ошибке. Никаких дополнительных, смущающих пользователя сообщений, SQL Server не показал. Выполняемый код обрамлен в блоке try и обработка ошибки в блоке catch. Получается чистый и ясный для понимания код. Если весь желаемый код прошел без ошибок, то код из блока Catch не будет вызван.

Теперь мы получим такой ответ от сервера:

Недостатки использования функции RaiseError

1 Если вспомнить, что показывала эта функция вызванная в Catch блоке, то заметим, что она ссылалась на строку номер 45, как источник проблем.

Однако в действительности ошибка произошла в строке номер 24, так где было написано

Insert into #tblExceptionTest([Phone Number]) Values(null)

В то время как функция ERROR_LINE() возвращает всегда реальное место возникновения ошибки. Еще один способ, чтобы показать работу новых функций будет такой:

В этом случае движок SQL Server выдаст такое сообщение:

Из чего можно заключить, что использование RaiseError не дает возможности указать на реальное место в скрипте, где произошла исключительная ситуация.

2 Следующий недостаток функции RaiseError состоит в том, что нет возможности повторно инициировать тоже самое исключение, для передачи вверх по иерархии вызовов. Так, если переписать блок Catch как показано ниже

То полученное сообщение об ошибке будет таким:

Msg 2732, Level 16, State 1, Line 46 Error number 515 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000

Причной этого является то, что для инициирования нового сообщения об ошибке, номер ошибки должен содержаться в таблице sys.messages.

Обработка ошибок в SQL Server 2011 (Denali)

Упомянутые выше недостатки функции RaiseError могут быть успешно преодолены с помощью новой команды Throw.

Первый недостаток функции RaiseError, на который мы указали ранее, невозможность сослаться на точную строку возникновения ошибки. Рассмотрим насколько далеко от места возникновения ошибки мы оказываемся при использовании команды Throw.

Перепишем блок Catch с использованием команды Throw.

Это точно то место, где произошла ошибка. Что ж, работает пока на отлично.

Вторым недостатком было то, что функция RaiseError не может повторно инициировать исключение потому, что RAISE ERROR ожидает номер ошибки, который хранится в таблице sys. messages. Команда Throw не ожидает, что номер ошибки должен быть из диапазона системной таблицы sys. messages, однако номер можно задать из диапазона от 50000 до 2147483647 включительно.

Снова изменим блок Catch в соответствии с новыми знаниями.

Результатом возникновения исключения будет

Msg 50001, Level 16, State 1, Line 45 Attempt to insert null value in [Phone Number] is not allowed

Получим сообщение об ошибке следующего плана:

Msg 208, Level 16, State 0, Line 3 Invalid object name ‘tblInvalid’.

Получается что почти невозможно перехватить такие типы ошибок.

Но. Как всегда есть небольшой трюк как осуществить желаемое. Основная идея в том, чтобы сделать две хранимых процедуры и вызывать одну из другой в блоке Try…Catch и ловить исключение. Для доказательства нашего предположения используем для экспериментов следующий скрипт.

При запуске процедуры ExternalStoredProc получим сообщение:

И панель Result отобразит следующие данные:

Что нам и требовалось!

Теперь немного объяснений как работает код. У нас есть 2 хранимых процедуры: usp_InternalStoredProc и usp_ExternalStoredProc. В usp_InternalStoredProc мы пытаемся вставить запись в несуществующую таблицу #tblInnerTempTable, в результате чего получаем исключительную ситуацию, которая в свою очередь отлавливается внешним блоком Catch, расположенным во внешней процедуре.

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

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