Catch try sql: Обработка ошибок в языке T-SQL — конструкция TRY CATCH | Info-Comp.ru

Обработка ошибок в языке T-SQL — конструкция TRY CATCH | Info-Comp.ru

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

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

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

Поэтому в языке Transact-SQL существует специальная конструкция TRY…CATCH, она появилась в 2005 версии SQL сервера, и которая используется для обработки ошибок. Если кто знаком с другими языками программирования, то Вам эта конструкция скорей всего знакома, так как она используется во многих языках программирования.

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Конструкция TRY CATCH в T-SQL

TRY CATCH – это конструкция языка Transact-SQL для обработки ошибок. Все, что Вы хотите проверять на ошибки, т.е. код в котором могут возникнуть ошибки, Вы помещаете в блок TRY. Начало данного блока обозначается инструкцией BEGIN TRY, а окончание блока, соответственно, END TRY.

Все, что Вы хотите выполнять в случае появления ошибки, т.е. те инструкции, которые должны выполниться, если в блоке TRY возникла ошибка, Вы помещаете в блок CATCH, его начало обозначается BEGIN CATCH, а окончание END CATCH. Если никаких ошибок в блоке TRY не возникло, то блок CATCH пропускается и выполняются инструкции, следующие за ним. Если ошибки возникли, то выполняются инструкции в блоке CATCH, а после выполняются инструкции, следующие за данным блоком, иными словами, все инструкции, следующие за блоком CATCH, будут выполнены, если, конечно же, мы принудительно не завершили выполнение пакета в блоке CATCH.

Сам блок CATCH не передает никаких сведений об обнаруженных ошибках в вызывающее приложение, если это нужно, например, узнать номер или описание ошибки, то для этого Вы можете использовать инструкции SELECT, RAISERROR или PRINT в блоке CATCH.

Важные моменты про конструкцию TRY CATCH в T-SQL

• Блок CATCH должен идти сразу же за блоком TRY, между этими блоками размещение инструкций не допускается;
• TRY CATCH перехватывает все ошибки с кодом серьезности, большим 10, которые не закрывают соединения с базой данных;
• В конструкции TRY…CATCH Вы можете использовать только один пакет и один блок SQL инструкций;
• Конструкция TRY…CATCH может быть вложенной, например, в блоке TRY может быть еще одна конструкция TRY…CATCH, или в блоке CATCH Вы можете написать обработчик ошибок, на случай возникновения ошибок в самом блоке CATCH;
• Оператор GOTO нельзя использовать для входа в блоки TRY или CATCH, он может быть использован только для перехода к меткам внутри блоков TRY или CATCH;
• Обработка ошибок TRY…CATCH в пользовательских функциях не поддерживается;
• Конструкция TRY…CATCH не обрабатывает следующие ошибки: предупреждения и информационные сообщения с уровнем серьезности 10 или ниже, разрыв соединения, вызванный клиентом, завершение сеанса администратором с помощью инструкции KILL.

Функции для получения сведений об ошибках

Для того чтобы получить информацию об ошибках, которые повлекли выполнение блока CATCH можно использовать следующие функции:

• ERROR_NUMBER() – возвращает номер ошибки;
• ERROR_MESSAGE() — возвращает описание ошибки;
• ERROR_STATE() — возвращает код состояния ошибки;
• ERROR_SEVERITY() — возвращает степень серьезности ошибки;
• ERROR_PROCEDURE() — возвращает имя хранимой процедуры или триггера, в котором произошла ошибка;
• ERROR_LINE() — возвращает номер строки инструкции, которая вызвала ошибку.

Если эти функции вызвать вне блока CATCH они вернут NULL.

Пример использования конструкции TRY…CATCH для обработки ошибок

Для демонстрации того, как работает конструкция TRY…CATCH, давайте напишем простую SQL инструкцию, в которой мы намеренно допустим ошибку, например, попытаемся выполнить операцию деление на ноль.

   
   --Начало блока обработки ошибок
   BEGIN TRY
        --Инструкции, в которых могут возникнуть ошибки
        DECLARE @TestVar1 INT = 10, 
                        @TestVar2 INT = 0, 
                        @Rez INT
        
        SET @Rez = @TestVar1 / @TestVar2

   END TRY
   --Начало блока CATCH
   BEGIN CATCH
        --Действия, которые будут выполняться в случае возникновения ошибки
        SELECT ERROR_NUMBER() AS [Номер ошибки],
                   ERROR_MESSAGE() AS [Описание ошибки]
        SET @Rez = 0       
   END CATCH

   SELECT @Rez AS [Результат]

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

У меня на этом все, надеюсь, материал был Вам полезен, пока!

Нравится1Не нравится

[Перевод] Обработка ошибок и транзакций в SQL Server. Часть 1. Обработка ошибок – быстрый старт

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

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

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

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

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

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

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

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

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

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

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

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

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

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

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

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

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

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

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

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

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

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

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

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUP\RESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

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

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

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

Триггеры

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

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

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

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

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

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

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

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

SET XACT_ABORT, NOCOUNT ON

Блоки try-catch для программистов на T-SQL_часть 3

• Другие части статьи:
• 1
• 2
• 3
• вперед »

TRY/CATCH, XACT_STATE, XACT_ABORT и транзакции.

Мы уже обсудили довольно много аспектов перехвата ошибок, но не коснулись аспекта, возможно, главного: а как вся эта «музыка» работает с такой вещью как транзакция? Ведь наши «потенциально опасные» инструкции исполняются обычно в рамках как-раз таки транзакций! Если ответить на поставленный вопрос коротко, то это будет «все работает». Но сказать что «все работает и нет никаких нюансов» было бы непростительной самонадеянностью, как раз к нюансам взаимодействия блоков перехвата и транзакций давайте переходить.

Стало быть, прежде всего что следует себе уяснить так это полную «отвязанность» двух конструкций друг от друга. Никто не «главный» и никто не «подчиненный». Никто не обязан быть блоком «объемлющим» и никто не обязан быть блоком «вложенным». Вполне может быть одно и не быть другого. Однако если все же есть и первое, и второе, то возникают вполне четко выраженные обязательства по отношению друг к другу. Как у нас вообще могут «сойтись» в одной точке кода и транзакции, и блоки перехвата и обработки? Очевидно — что-то во что-то вкладывается, как же еще? Не менее очевидно что способов вложения всего два. Или «TRY в транзакции»:

Или «транзакция в TRY»:

Как было отмечено выше, в связи с полным отсутствием иерархических взаимоотношений между разбираемыми синтаксическими элементами нет и не может быть вложения «правильного» и «ошибочного». Оба показанных варианта функционально эквивалентны. Выбирайте любой исходя из эстетических своих предпочтений. Если вам это интересно, лично автору блога на котором вы в настоящий момент находитесь ближе и «родней» вариант номер 2. Он предпочитает что бы все вещи относящиеся к одной транзакции, включая и открытие оной, происходили в одном «месте», блоке. Однако, повторю, выбор между двумя показанными подходами — вопрос стиля, а не технической корректности кода.

Хорошо, вот эти две вещи соединились — какое главное правило такого «слияния»? Очень простое: если в блоке TRY у вас есть «ваша» открытая транзакция (открыли ли вы ее методом 1, или методом 2 — не важно), то совершенно все (без исключения!) пути исполнения вашего кода как в блоке TRY, так и в блоке CATCH обязаны вести к завершению транзакции хоть фиксацией («коммит»), хоть откатом («ролбэк»). Худшее что вы можете сделать — покинуть блок TRY/CATCH оставив транзакцию в «подвешенном» состоянии. Ну а каков план реализации этого «очень простого правила»? А вот тут и начинаются обещанные нюансы…

План «генеральный», в общем-то, уже показан в двух отрывках кода выше. Если блок TRY пройден почти до конца, то перед самой меткой END TRY мы транзакцию фиксируем — ведь мы не испытали никаких проблем при ее исполнении, верно? Конечно, никто не может нам запретить из анализа некоторой информации транзакцию в той же точке откатить, но чаще — фиксируем. Ну а если мы «свалились» в блок CATCH, то сразу же за меткой BEGIN CATCH (или, по крайней мере, недалеко от нее) мы транзакцию откатываем. А есть ли тут у нас свобода выбора безусловно присутствующая в блоке предыдущем? Можем ли мы находясь в блоке CATCH транзакцию все же зафиксировать? It, как говорится, depends. Давайте к коду:

Результаты:

и

Транзакция открыта и находится в фиксируемом состоянии, начинаю фиксацию…

Стало быть, один факт установлен доподлинно — есть варианты! И даже в блоке CATCH. Но, во-первых, обратите внимание, что из двух легальных значений колонки ID зафиксировалось (да и было вставлено, на самом деле) лишь первое. Ведь уходя в CATCH мы в TRY не возвращаемся, не забыли? А, во-вторых, обращают на себя внимание две новых синтаксических конструкции: XACT_ABORT до начала транзакции и XACT_STATE в блоке CATCH. «Разбор полетов» начнем с последней.

Функция XACT_STATE сообщает нам о состоянии текущей транзакции «внутри» которой мы находимся в момент вызова этой функции. XACT_STATE возвращает нам целое число и причем варианта всего три:

• 0 — никакой транзакции вообще нет, не о чем и беспокоиться в плане ее корректного завершения;
• 1 — активная транзакция есть и находится в состоянии допускающим как ее откат, так и ее фиксацию, выбор за нами;
• -1 — тоже активная транзакция есть, но она перешла в специальное «нефиксируемое» состояние. Единственно возможная операция с такой транзакцией — полный и безусловный ее откат. Который, тем не менее, не выполняется движком сервера в автоматическом режиме. И указание инструкции ROLLBACK остается прерогативой и обязанностью (причем одной из главнейших) нашего T-SQL программиста.

Ну с первым значением вопросов нет, отсутствует транзакция — так и нам спокойнее. А вот с двумя вторыми значениями? Отчего бывает так, а бывает и эдак? А это зависит от серьезности той ошибки что привела нас в блок CATCH. Если ошибка «жесткая» мы получаем -1, если «мягкая» — +1. Кстати говоря, если та же самая транзакция будет исполняться вне блока TRY/CATCH то «жесткая» ошибка приведет к полной отмене всей транзакции, со всеми инструкциями ее составляющими. А ошибка «мягкая» приведет к выкидыванию из состава транзакции лишь «плохой» инструкции, остальные инструкции той же транзакции будут продолжены. Скажем исполнение вот такого кода:

приведет к вставке двух строк и плюс к предупреждению

Cannot insert the value NULL into column ‘id’, table ‘tempdb.dbo.T1’; column does not allow nulls. INSERT fails.

И это все потому, что вставка нелегального значения — ошибка «мягкая». Измените строчку ее вызывающую, т.е.

на

и ни одна строка вставлена не будет, подавляющее число ошибок команд группы DDL — «жесткие». И, как вы правильно понимаете, если мы исправленный вариант нашей транзакции вновь «обернем» в TRY/CATCH, то сообщение будет

Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…

и уж конечно ни одна из вставляемых строк в таблице T1 обнаружена не будет. Так что разница между 1 и -1 возвращаемыми нам функцией XACT_STATE сводится к «жесткости» ошибки.

Хорошо, а роль второй инструкции, XACT_ABORT какова? А вот какова. Если значение этой опции (как и все прочие «SET-опции» она устанавливается для текущей сессии соединения с сервером) OFF — то «мягкие» ошибки будут «мягкими». А «жесткие», как легко догадаться, «жесткими». Кстати, каждое новое подключение начинает свою работу именно с этого значения обсуждаемой опции. А вот переводя эту опцию в значение противоположное мы заявляем, что хотим считать все ошибки — «жесткими». Ну и конечно хотим что бы движок сервера разделял эту нашу точку зрения. Например измените в скрипте чуть выше, где эта опция упомянута, ее значение на ON. В остальном оставьте скрипт как есть. Вы помните, что в предыдущий раз у нас одна строка вставилась в таблицу T1 и эта вставка была успешно зафиксирована. А что теперь?

Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…

и полное отсутствие записей в таблице T1.

Ну а какое значение опции XACT_ABORT «правильное»? Под каким лучше работать? А вот это — хороший вопрос если вы планируете начать новую «священную войну» на техническом форуме. Скажем так: если бы «хорошесть» того или другого варианта была доказана неопровержимо и на 120%, то команде разработчиков, надо думать, не составило бы труда прописать это «победившее» значение в код движка, а опцию XACT_ABORT просто убрать с глаз долой. Ан нет — не прописывают и не убирают. Апологеты обоих подходов приводят свои доказательства на тему «как надо жить», однако «финальный довод» пока не дался никому. Автор данных строк принадлежит лагерю сторонников опции ON и вот почему. Во-первых, при работе с распределенными запросами и распределенными транзакциями данное значение должно быть выставлено для XACT_ABORT без всяких дискуссий, просто потому что так предписывает BOL. Но распределенные запросы — случай частный и специфичный, так что аргумент получается хоть и неопровержимый, но уж очень «узконаправленный», не масштабный. Однако есть и более весомое во-вторых. Вот код:

Запустите показанный код в студии и пока он «вращается» в заданной 10-ти секундной задержке прервите его выполнение нажав кнопку Cancel Executing Query на панели студии либо выбрав одноименный пункт в меню Query. Если бы наш клиент предполагал выход из запроса по слишком долгому времени потраченному на его исполнение (query timeout) то можно было бы и выждать данный отрезок времени не «снося» запрос принудительно — все дальнейшие замечания не потеря ли бы своей актуальности. Так вот, отменив запрос мы видим что блок CATCH не отработал. И это разумеется, и это ожидаемо, ведь выполнение кода отменено, не правда ли? А вот о чем многие не подозревают, так это то, что транзакция осталась открытой. В чем легко убедиться открыв в редакторе студии еще одну вкладку и выполнив там

И будет она, транзакция наша, так и «висеть», пока клиент не удосужится сделать ей принудительный откат. Но «висит-то» она не просто «висит»: она сохраняет все свои блокировки, на ее поддержку тратятся ресурсы и т.д. Да, если клиент «отвалится» (закроет соединение) то требуемый откат совершит сам движок, без участия клиента. Однако тут есть большая «засада», помимо очевидной, что клиент еще должен «отвалиться» и не факт что это случится спустя микросекунду после отмены им «долгоиграющего» запроса. «Засада» заключается в том, что большинство современных платформ создания приложений пользователя (а энтузиасты ваявшие софт на чистом ассемблере давно перевелись) используют парадигму «пула подключений» (connection pool). И с этой парадигмой закрытие клиентом подключения вовсе не означает физический обрыв связи, подключение просто возвращается пулу «как есть» и готово со всем своим наследством в виде «висячей» транзакции «отдаться» новому клиенту. А потому транзакция не будет закрыта до тех пор, пока она или не будет закрыта соответствующей командой с клиента (возможно и не тем, который ее «повесил»), или пока подключение с нею не «уйдет» из пула, либо, всего надежней, пока не будет выключен весь пул целиком, например в виду завершения работы платформы как таковой.

Откатив из первой вкладки редактора проблемную транзакцию (ROLLBACK TRANSACTION), изменим в последнем скрипте значение опции на пропагандируемый автором блога ON. Вновь запустим тот же запрос и вновь прервем. Да, CATCH вновь не отработал — мы ж ему не дали этого сделать. Но и транзакции нет! В чем можно убедиться вновь запросив динамическое представление sys.dm_tran_active_transactions, как это было в первом эксперименте. Нету! Закрыто принудительно движком без всякого нашего вмешательства. Что, по мнению многих SQL-специалистов и автора блога так же, много, много лучше чем в варианте со значением OFF. Разумеется, вы можете сказать, что раз клиент открыл транзакцию, а потом отказался от запроса, то… «соображать же надо!». Надо, кто ж спорит. И правильно написанный клиент не только сделает Cancel, но и еще ROLLBACK своей же транзакции, причем сначала второе и лишь затем первое. Да вот кабы все те клиенты да были правильно написаны…

Можно привести и третий аргумент в поддержку опции ON. Как кажется автору (и, могу вас уверить, не только ему) поведение «ошибка — откат всей транзакции» является интуитивно ожидаемым, а поведение «ошибка — транзакция продолжается» является контр-интуитивным. Однако тут у каждого своя интуиция, не буду спорить. Как бы то ни было, опыт автора по написанию кучи строк T-SQL кода однозначно говорит о том, что опция ON обеспечивает нас желаемым и ожидаемым поведением транзакций и блоков TRY/CATCH в 99% случаев. Я ни разу не встречал ситуации когда транзакцию прерванную или превысившую свой тайм-аут не нужно было бы откатывать. А если это точно нужно — чего тянуть? Оставшиеся 1% когда оправдано применение опции OFF это случаи поиска и «отлова» конкретной ошибки в моем T-SQL коде и причем я еще хочу продолжить его исполнение после нахождения той ошибки за которой «охочусь». Повторю, что число таких случаев, с точки зрения автора, исчезающе мало, хоть и не равно абсолютному нулю.

Итак, теперь у читателей данного материала достаточно фактов что бы сделать свой выбор правильного (не абсолютно правильного, а правильного для конкретно их системы, за разработку/поддержку которой они отвечают) значения для этой «скользкой» опции — XACT_ABORT. Помимо всего изложенного выше при осуществлении такого выбора пусть читатели еще учтут:

• значением по умолчанию для данной опции, как уже отмечалось, является OFF. Так что утвердить безусловный, полный и необсуждаемый ON в масштабах предприятия/решения/команды разработки не так-то просто, людям свойственно стараться «плыть по течению». Потребуются контроль, дисциплина и самодисциплина;
• значение ON дает микроскопический, совершенно не ощутимый на практике плюс для производительности в силу того, что при таком значении движок мгновенно принимает решении об откате проблемной транзакции. Однако, повторю, плюс этот будет измеряться такими долями микросекунд, что автор отмечает его здесь исключительно для полноты изложения, а не в качестве аргумента для принятия решения;
• ни та, ни другая опция не имеют отношения к вопросу «произойдет ли сваливание в блок CATCH». Иными словами, если данная ошибка отправляет поток исполнения в блок CATCH — он отправится туда и в случае ON, и в случае OFF. Потому что это определяется не опцией XACT_ABORT, а… чем? Правильно — номером серьезности возникшей ошибки. XACT_ABORT отвечает на вопрос будут ли у нас варианты с нашей транзакцией в том же блоке, или нас ожидает ROLLBACK, только ROLLBACK и ничего кроме него.

С практической точки зрения, автор рекомендует своим читателям начинать любой T-SQL код со строки SET XACT_ABORT ON. Если этот код будет кодом создания новой хранимой процедуры, то просто возведите степень настойчивости данной рекомендации в квадрат. Если эта новая хранимая процедура планирует работу с явными транзакциями — в куб. А с еще более практической точки зрения заведите себе шаблон (template) для оформления своих хранимых процедур со всеми «обвесами» по умолчанию. В качестве «точки старта» можете воспользоваться шаблоном автора:

Разумеется, создать шаблон годящийся для 100% новых хранимых процедур нереально, хорошо если «степень покрытия» будет хотя бы процентов 50. Тем не менее у автора в его студии показанный шаблон «сидит» в Template Explorer (клавиши Ctrl+Alt+T, если вы не в курсе как его, обозреватель этот, открыть; либо ищите в меню View) под именем #Procedure_Main#, как это легко видеть из иллюстрации справа, и автор вполне доволен. Было б неплохо обсудить код этого шаблона, но это увело бы нас с темы «блоки TRY/CATCH» в тему «управление транзакциями», а это вопрос хоть и безусловно интересный, но свой и отдельный. Так что оставим такой разговор до лучших времен. Отмечу лишь, что ошибки генерируемые процедурами построенными на базе показанного шаблона будут иметь вот такой аккуратный и «причесанный» вид:

Msg 50000, Level 16, State 1, Procedure ABC, Line 56
**From ABC**: ErrNum=515:ErrSt=2:ErrMsg=»Cannot insert the value NULL into column ‘id’,
table ‘tempdb.dbo.T1’; column does not allow nulls. INSERT fails.»

Процедура ABC была создана из обсуждаемого шаблона, запущена и попыталась вставить некорректное значение в таблицу T1. Результат — перед вами.

Как правильно использовать обработчики исключений.

Пожалуй к текущей точке повествования мы с вами разобрали решительно все технические моменты относящиеся к технологии перехвата ошибок в языке T-SQL прямо или косвенно. Остались вопросы больше архитектурные, отделяющие приложения с правильным дизайном от приложений без такового. То есть нам осталось понять — когда и в каких обстоятельствах блоки TRY/CATCH уместны, а когда не очень.

Когда эта технология только появилась (а случилось это, напомню, лишь в версии 2005-й нашего сервера) на волне новизны и эйфории от такого «приближения к языкам C++/C#» было поветрие включать весь и каждый T-SQL код в блоки обработки ошибок. Что, разумеется, правильным не является ни разу. Соображения тут такие:

• включать 100% нашего кода в блок TRY однозначно не нужно! Когда мы пишем что-то в этом блоке мы, подспудно, говорим сами себе: «так, а вот тут у нас во время исполнения могут случится грабли». Скажем в наших примерах выше, мы включаем оператор PRINT в обсуждаемый блок, но делаем это с целью исключительно демонстрационной. Делать это на полном серьезе лишено какого-либо смысла, вы можете описать потенциальные «грабли» при выводе строки текста от сервера к клиенту? Разумеется, если мы это и сделаем ничего плохого не случится, просто код будет захламлен лишними синтаксическими элементами, да будут фигурировать блоки CATCH, чьи инструкции не имеют ни малейшего шанса быть исполненными хоть раз в жизни. Поэтому разумный подход — включать в блоки TRY только «потенциально проблемный» код. Не менее понятно, что если мы включаем в TRY целую транзакцию, а одна из ее инструкций тот самый PRINT — это совершенно нормально, не «резать» же транзакцию только что бы вычленить из нее эту «сверхнадежную» команду. А вот если вся транзакция состоит только из инструкций PRINT, то, конечно, она сама представляется очень странной (к чему тут транзакция?), но уж определенно блок TRY здесь и подавно не нужен. Итак, в фокус нашего внимания должны попадать лишь инструкции несущие «потенциальную угрозу».
• но и правило 100% «потенциально проблемного» кода должно быть включено в блок TRY тоже неверно. Скажем тот ж INSERT однозначно может иметь проблемы при выполнении сколь бы элементарен не был код этой инструкции (мы это уже обсуждали — журнал транзакций и т.п.). Вы эту возможную ошибку ясно видите, но вам решительно нечего «сказать по поводу»! Вы не хотите заморачиваться приращением журнала и повторной попыткой сделать INSERT еще раз, вы не хотите логировать факт ошибки в некоторый приемник, вы не возражаете если клиент получит стандартное серверное сообщение о произошедшей проблеме и т.д. Снова — к чему вам TRY? Что вы напишете в парном ему блоке CATCH? А поэтому еще более разумный подход — включать в блоки TRY только «потенциально проблемный» код, причем только тот, для которого у нас есть некий план, идея по преодолению означенных проблем или, как минимум, по их сохранению (не самих проблем, разумеется, а информации о них) для будущего анализа. Если никаких таких идей у нас нет — незачем и создавать платформу для их реализации.

Не менее важный и сложный вопрос звучит так: «свалившись» в блок CATCH и что-то там сделав (возможно даже нивелировав последствия случившейся ошибки), нужно ли скрыть произошедшую ошибку, «подавить» ее, или же применив RAISERROR следует дать знать вызывающему коду о постигшей нас неприятности? Вот тут все очень непросто. Дело в том, что по сути, перехватывая исключение, вы заявляете — «я знаю как нам быть в этом случае»! Однако очень сомнительно, что вы и вправду готовы преодолеть все возможные ошибки приводящие код в блок CATCH, даже с учетом, что это лишь подмножество всех ошибок имеющихся в запасе сервера. Слишком велико даже это подмножество, число его элементов исчисляется тысячами. Тут «во всей красе» проявляется молодость технологии перехвата исключений реализованная в языке T-SQL. Дело в том, что в языках использующих ту же технологию много-много лет, есть, по сути, негласное правило: данный блок CATCH работает с одной конкретной ошибкой. Для этого у означенного блока (т.е. прямо у ключевого слова CATCH) есть свой параметр означающий код, или тип, ошибки который и будет обрабатываться именно данным блоком, а блок TRY может иметь сколько угодно «прицепленных» к нему вот таких «узконаправленных» блоков CATCH. Если бы тоже самое было реализовано в T-SQL, то наша хранимая процедура могла бы иметь такой вид (псевдокод):

BEGIN TRY
    …
    …
END TRY
BEGIN CATCH (переполнение журнала)
    добавить место в LDF файл
    повторить транзакцию
END CATCH
BEGIN CATCH (деление на ноль)
    вернуть в качестве результата -1
END CATCH
BEGIN CATCH (попытка дублировать первичный ключ)
    с помощью RAISERROR сообщить клиенту расширенную информацию о дублирующей записи
END CATCH

К сожалению, нас вынуждают безусловно перехватывать все ошибки без всякой фильтрации, что в тех же языках поднаторевших в перехвате рассматривается всегда (почти) как дурной тон. Оно, конечно, можно эмулировать до некоторой степени приведенный выше псевдокод через анализ в единственном блоке CATCH номера ошибки (спасибо, что есть хотя бы удобная функция, ERROR_NUMBER), но тогда в определенном смысле мы откатываемся назад, во времена 2000-го сервера, когда точно так же анализировали @@ERROR и все было «просто и красиво», при условии, конечно, что вы находите спагетти-код красивым. То есть никакого четкого деления кода на блоки при таком подходе нам уже не видать, значимость технологии перехвата как таковой сильно нивелируется. Тем не менее, и еще раз к сожалению, если у вас нет плана по обработке именно всех ошибок (например — заношу их все в таблицу ErrorsTbl и дело с концом) обращения к ERROR_NUMBER и, как следствие, ветвления в коде блока CATCH вам неизбежать, увы.

Но, допустим, тем или иным алгоритмом вы перехватили ошибку, проанализировали ее, и даже, может быть, ее «обошли» (придумали что сделать с переполненным журналом, с нулем в знаменателе и т.п.). Нужно ли вам привлекать RAISERROR для повторного «броска» той же ошибки, или, может быть, ошибки ее замещающей? Или правильно будет такую исправленную ошибку «проглотить»? Снова — вопрос не предполагающий короткое «да» или «нет»… Скажем осторожно: скорее да («бросать» повторно), чем нет («проглатывать»), и вот почему. Дело в том, что на T-SQL пишется серверный код. Который потом будут использовать клиентские приложения. Приложений могут быть десятки разновидностей. Простые и сложные, «навороченные» и «для самых маленьких», для десктопа и для Web-а… В момент создания вашего T-SQL вы про весь этот «зоопарк» знать не можете. Теперь, допустим, вы кодируете вставку новой строки. Перехватили исключение, выяснили что причина в отсутствии места в файле лога (LDF), прирезали, повторили вставку — OK. Казалось бы, клиент хотел вставить строку и строка вставлена, ну чего его дергать сообщениями «а знаешь чего было»? Однако не исключено, что этот ваш код будет вызываться десктопным приложением написанным специально и исключительно для администраторов баз данных. И вот им, факт такого прирезания в рабочие часы может быть очень даже интересен. Ведь как знают читатели статьи Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер изменения физических размеров файла журнала транзакций лучше проводить в часы вечерние и ночные. А случившееся может потенциально означать, что администратор неверно оценивает скорость прирастания данных (или объема транзакций) в системе за которую он отвечает. А то и вообще это может быть намеком администратору на проблемы с усечением лог-файла.

Одним словом — доведите информацию о случившемся до клиента, позвольте программисту последнего решить, стоит ли дергать конечного потребителя решения всякими «Warning-окошками» или нет. Языки на которых пишутся современные клиенты имеют куда как более гибкие механизмы по отлову исключений, обработке их, и, если так решит программист, по их «проглатыванию». Так же заметьте себе, что у RAISERROR есть вариант вызова, когда указывается не текст ошибки, а ее номер (в этом случае текст должен быть заранее добавлен в представлении каталога sys.messages; процедура sp_addmessage поможет вам в этом). Вы можете составить свой, «внутренний» (только для вашего решения) рейтинг «вбрасываемых» вами ошибок по степени их серьезности. С учетом, что все ошибки определяемые нами, программистами на T-SQL, начинают нумерацию с 50000, подобный рейтинг может быть таким:

• меньше 50100 — «легкие» ошибки, намек программисту клиента, что в целом можно и игнорировать, или выводить фоновым процессом в файл регистрации событий, может быть в лог приложений OS Windows, без уведомления пользователя;
• более 50100 — «средние» ошибки, намек о необходимости извещения конечного пользователя и, возможно, запроса у последнего разрешения на продолжение работы в сложившихся обстоятельствах;
• более 50200 — «суровые» ошибки, намек о безусловном извещении клиента и, с большой степенью вероятности, о необходимости завершении подключения к серверу со стороны клиента;
• более 50300 — «фатальные» ошибки, сессия принудительно закрывается сервером без всяких намеков.

Ну или что-то в таком роде. Самое главное, кроме ну очень фатальных ситуаций, оставьте право принятия окончательного решения за программистом приложения! Предоставив для этого последнему всю информацию которой располагаете. Пусть номера ваших ошибок и их деление по диапазонам будут именно подсказкой для клиента, но не принуждением того к определенным действиям (либо бездействию). Короткий практический вывод данного раздела:

За редким (хотя и возможным) исключением, код блока CATCH обязан быть завершен инструкцией RAISERROR сообщающей ту или иную информацию вызывающему коду.

Если вы обратите внимание, то шаблон хранимой процедуры предложенный ранее автором написан в полном соответствии с этим нашим выводом. Что, конечно же, не означает что текст его «выбит в камне» и не подлежит редактированию. Если после тщательного анализа вы приходите к выводу что данный CATCH будет успешно обрабатывать все исключения и сообщать о них коду «вышестоящему» (и в том числе клиенту) резона нет, то проанализируйте все аргументы в пользу такого вывода еще раз, после чего смело стирайте инструкцию RAISERROR в упомянутом шаблоне.

Контрольная работа.

Автор надеется, что статья, к чьим финальным строкам вы подошли уже совсем вплотную, оказалась для вас полезной и познавательной. Мы достаточно подробно и всесторонне осмотрели и обсудили один из важнейших механизмов предлагаемых в наше распоряжение языком T-SQL — механизм перехвата и обработки ошибок. Уверенное владение данным механизмом необсуждаемый «must have» любого мало-мальски профессионального программиста, он обязан применять его в своем коде и делать это разумно, с учетом конкретных обстоятельств для каждого программного модуля. Полагаю, читатели внимательно следовавшие за автором на протяжении всего материала, запустившие и проверившие в своих студиях все приведенные в нем T-SQL скрипты вполне готовы к применению этого важного механизма на практике. Дабы вы могли лишний раз убедиться в такой своей готовности — обещанная в начале статьи контрольная работа. Работа состоит из шести несложных, полностью независимых, и готовых к запуску отрывков T-SQL кода. Для каждого отрывка необходимо ответить на два аналогичных вопроса:

• напечатает ли инструкция PRINT указанную ей строку?
• инструкция SELECT каждого отрывка может вернуть резалт-сет состоящий максимум из пяти строк, содержащих по одной цифре от 1 до 5 в каждой. Указать реальный состав резалт-сета или указать, что резалт-сет будет пуст.

Отвечать на вопросы следует, разумеется, без запуска соответствующего отрывка, но после ответа его проверка в студии вполне поощряется, хотя можно и просто открыть свернутый в настоящий момент текст содержащий правильный ответ. Сразу хочу предупредить, что скрипты лишь на 70% проверяют знание механики работы блоков TRY/CATCH, а на 30% — элементарную внимательность. С другой стороны, как автор не устает повторять на своих курсах, «хороший программист должен обладать всего тремя чертами характера: внимательностью, внимательностью, и — самая главная черта — внимательностью». Так что эти 30% лишними не станут, уверяю вас. Удачи в прохождении теста и — до новых встреч на страницах блога sqlCMD.ru, увидимся, пока!

Скрипт 1

Смотреть ответ для Скрипт 1

PRINT: срабатывает
SELECT: 1,2,4,5

Скрипт 2

Смотреть ответ для Скрипт 2

PRINT: срабатывает
SELECT: 1,2,4,5

Скрипт 3

Смотреть ответ для Скрипт 3

PRINT: срабатывает
SELECT: 4,5

Скрипт 4

Смотреть ответ для Скрипт 4

PRINT: срабатывает
SELECT: 1,2

Скрипт 5

Смотреть ответ для Скрипт 5

PRINT: срабатывает
SELECT: 1,2

Скрипт 6

Смотреть ответ для Скрипт 6

PRINT: НЕ срабатывает
SELECT: пустой резалт-сет

• Другие части статьи:
• 1
• 2
• 3
• вперед »

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

Новое полезное дополнение для SQL Server 2011 (Denali) ­– выражение Throw. Разработчики на .Net уже догадались наверно, где и как оно будет использоваться.

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

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

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

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

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'tbl_ExceptionTest' AND type = 'U')
	DROP TABLE tbl_ExceptionTest
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_ExceptionTest](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Phone Number] [int] NOT NULL,
	CONSTRAINT [PK_tbl_ExceptionTest] PRIMARY KEY CLUSTERED
)

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

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

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

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

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

-- Если таблица #tblExceptionTest уже создана, то удалить ее.
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest
End

-- Создать временную таблицу #tblExceptionTest
Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

--Начало транзакции
Begin Transaction TranExcp__2000_@@Error

 -- объявление переменных
 -- локальная переменная хранящая номер ошибки из @@ERROR
Declare @ErrorNum int  
-- локальная переменная работающая как счетчик
Declare @i int

-- инициализация переменных
Set @i =1

-- начало операции
While(@i <= 4)
Begin
	-- симуляция ситуации когда пользователь пытается ввести null в колонку Phone Number
	If(@i = 4)
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(null)
		Set @ErrorNum = @@ERROR
	End
	Else
	 -- все данные будут внесены успешно
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
	End

	Set @i = @i +1

End -- конец while

-- если есть ошибки, вывести их и откатить транзакцию
If @ErrorNum <> 0
Begin
	Rollback Transaction TranExcp__2000_@@Error
	-- показать специальное сообщение об ошибке
	RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)
End

-- сохранить изменения
Else If @ErrorNum = 0
Begin
	Commit Transaction TranExcp__2000_@@Error
End

-- показать записи
Select * from #tblExceptionTest

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

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

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

• Значение переменной @@Error должно быть проверено сразу после выполнения запроса/команды.
• Так как @@Error постоянно меняется, то мы вынуждены заводить отдельную переменную для сохранения и вывода кода ошибки.
• Вместе со специальным сообщением об ошибке указывающей на логический смысл ошибки выводится техническая информация, которая пользователям не интересна.

Если вы хотите узнать больше деталей и нюансов по использованию @@Error, то советую обратиться к статье про @@Error.

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

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

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

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

-- если таблица #tblExceptionTest существует, то удаляем ее
If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest
End

-- создаем временную таблицу
Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

-- начинаем транзакцию
Begin Transaction TranExcp__2000_@@TRANCOUNT

 --объявление переменных
 -- локальная переменная хранящая значение @@TRANCOUNT
Declare @TransactionCount int 
-- счетчик 
Declare @i int 

-- инициализация счетчика
Set @i =1


-- старт эксперимента
While(@i <= 4)
Begin
	-- симуляция ситуации когда пользователь пытается ввести null в колонку Phone Number
	If(@i = 4)
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(null)
		Set @TransactionCount = @@TRANCOUNT
	End
	Else
	-- все записи будут внесены успешно
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
	End
	Set @i = @i +1

End -- конец while

-- если есть ошибки, то уведомить об этом и откатить транзакцию
If @TransactionCount <> 0
Begin
	Rollback Transaction TranExcp__2000_@@TRANCOUNT
	-- показ специальной ошибки
	RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)
End
-- подтверждение изменений
Else If @TransactionCount = 0
Begin
	Commit Transaction TranExcp__2000_@@TRANCOUNT
End

-- вывод записей
Select * from #tblExceptionTest

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

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

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

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

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

Пример:

If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest
End

Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)


Begin Transaction TranExcp__2000_@@ROWCOUNT
Save Transaction TranExcp__SavePoint

Declare @RowCount int  
Declare @i int 

Set @i =1

While(@i <= 4)
	Begin
	If(@i = 4)
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(null)
		Set @RowCount = @@ROWCOUNT
	End
	Else
	Begin
		Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
	End
	Set @i = @i +1

End 

If @RowCount = 0
Begin
	Rollback Transaction TranExcp__SavePoint
	RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)
End
Else If @RowCount <> 0
Begin
	Commit Transaction TranExcp__2000_@@ROWCOUNT
End

Select * from #tblExceptionTest

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

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

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

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

If OBJECT_ID('tempdb..#tblExceptionTest') Is not null
Begin
	Drop Table #tblExceptionTest
End

Begin TRY
	Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null)

	Begin Transaction TranExcpHandlingTest_2005_2008
	
	Declare @i int  
	Set @i =1

	While(@i <= 4)
		Begin
		If(@i = 4)
		Begin
			Insert into #tblExceptionTest([Phone Number]) Values(null)
		End
		Else
		Begin
			Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678')
		End
		Set @i = @i +1

	End 

	Commit Transaction TranExcpHandlingTest_2005_2008

End Try
Begin Catch
	Begin
		Rollback Transaction TranExcpHandlingTest_2005_2008
		RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1)
	End
End Catch

Select * From #tblExceptionTest

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

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

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

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

Самое важное то, что Catch блок представляет набор функций для детального разбора причин ошибки и возможность информирования пользователя на должном уровне. Функции для разбора исключительной ситуации:

• ERROR_NUMBER
• ERROR_SEVERITY
• ERROR_STATE
• ERROR_LINE
• ERROR_PROCEDURE
• ERROR_MESSAGE

С помощью этих функций попробуем переписать Catch блок скрипта, что бы представлен до этого.

Begin Catch
	-- обработка ошибки
	Begin
		-- откат транзакции
		Rollback Transaction TranExcpHandlingTest_2005_2008
		SELECT
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_LINE() AS ErrorLine,
			ERROR_MESSAGE() AS ErrorMessage;
	End
End Catch

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

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

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

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

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

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

Begin Catch
	Begin
		Rollback Transaction TranExcpHandlingTest_2005_2008
		DECLARE @errNumber INT = ERROR_NUMBER()
		DECLARE @errMessage VARCHAR(500) = 'Attempt to insert null value in [Phone Number] is not allowed'

		RAISERROR('Error Number: %d, Message: %s', 16, 1, @errNumber, @errMessage)
	End
End Catch

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

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

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

Begin Catch
	Begin
		Rollback Transaction TranExcpHandlingTest_2005_2008
		RAISERROR(515, 16, 1)
	End
End 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.

Для более детального изучения функции RaiseError, рекомендуется к прочтению:

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

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

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

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

Begin Catch
	Begin
		Rollback Transaction TranExcpHandlingTest_2011;
		THROW
	End
End Catch

Вывод будет таким:

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

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

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

Begin Catch
	Begin
		Rollback Transaction TranExcpHandlingTest_2011;
		THROW 50001,'Attempt to insert null value in [Phone Number] is not allowed',1
	End
End Catch

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

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

На данный момент SQL Server предоставляет множество путей для отлова ошибок, но до сих пор не все ошибки могут быть пойманы с помощью блока Try…Catch. Например:

• Синтаксические ошибки отлавливаются редактором запросов в SSMS
• Неправильные имена объектов

Если попробовать подать на выполнение следующий скрипт:

Begin Try
	--неверное использование объекта tblInvalid
	Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26'))
End Try

Begin Catch
	--кидаем ошибку
	THROW
End Catch

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

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

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

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

-- проверить существование процедуры, если есть, то удалить
If Exists (Select * from sys.objects where name = 'usp_InternalStoredProc' and type = 'P')
	Drop Procedure usp_InternalStoredProc
Go

-- создать внутреннюю хранимую процедуру
Create Procedure usp_InternalStoredProc
As
Begin
	Begin Transaction TranExcpHandlingTest_2011
	Begin Try
		-- обращение к несуществующему объекту
		Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26'))
		-- закрытие транзакции
		Commit Transaction TranExcpHandlingTest_2011
	End Try
	Begin Catch
		If @@TRANCOUNT > 0 Rollback Transaction TranExcpHandlingTest_2011
		Print 'In catch block of internal stored procedure.... throwing the exception';
		-- инициирование исключения
		THROW
	End Catch
End
Go


-- скрипт для создания внешней хранимой процедуры
-- проверка существования процедуры, если есть, то удалить
If Exists (Select * from sys.objects where name = 'usp_ExternalStoredProc' and type = 'P')
	Drop Procedure usp_ExternalStoredProc
Go

-- создание внутренней хранимой процедуры
Create Procedure usp_ExternalStoredProc
As
Begin
	Begin Try
		-- вызов внутренней процедуры
		Exec usp_InternalStoredProc
	End Try
	Begin Catch
		Print 'In catch block of external stored procedure.... throwing the exception';
		SELECT
			 ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
		THROW
	End Catch
End
Go

-- вызов внешней процедуры
Exec usp_ExternalStoredProc

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

In catch block of external stored procedure.... throwing the exception

(1 row(s) affected)

Msg 208, Level 16, State 1, Procedure usp_InternalStoredProc, Line 8

Invalid object name 'tblInvalid'.

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

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

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

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

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

Incorrect syntax near ‘THROW’.

Больше детальной информации о THROW можно подчерпнуть из MSDN.

Переводы из цикла:

MS SQL Server 2011: Автономные базы данных, новый объект Sequence, оператор Offset, обработка ошибок, конструкция With Result Set, новое в SSMS.

Ловим ошибки в запросе к MS SQl и откатываем изменения — транзакции (transaction) и блок TRY-CATCH

Имеем запрос, меняющий данные на MS SQL Server. Задача: отловить возникшие при выполнении запроса ошибки и откатить все уже внесенные внутри запроса изменения к состоянию на момент его запуска. Помогут нам в этом нелегком деле транзакции и блок TRY-CATCH. Когда вообще возникает необходимость? Типичный пример из учебника, последовательное удаление двух строк из разных таблиц, имеющих один и тот же код:

DELETE FROM SOME_TABLE1 WHERE SOME_ID = SOME_VALUE
DELETE FROM SOME_TABLE2 WHERE SOME_ID = SOME_VALUE

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

Транзакция — это последовательность операций, которая должна или полностью выполниться успешно или не выполнится вообще. В Transact Sql она начинается командой BEGIN TRANSACTION и продолжается до выполнения команды отката ROLLBACK TRANSACTION или подтверждения COMMIT TRANSACTION. Само собой команду отката надо выполнять в случае ошибок. Как их отследить? Теоретически есть переменная @@ERROR, содержащая код ошибки или 0 в случае успешного выполнения, но на практике такой код писать нельзя

IF @@ERROR <> 0 ROLLBACK

так как переменная @@ERRROR переопределяется после каждой SQL-команды. То есть если первый DELETE вылетит с ошибкой, в переменную запишется код ошибки, если второй оператор выполнится успешно, то в переменную запишется ноль и мы ничтоже сумняшеся , подтвердим изменения.

И здесь нам придет на помощь блок TRY-CATCH, хорошо знакомый програмиирующим на C# (и не только) товарищам. Делает он хорошо знакомую вещь, отлавливает любую ошибку в блоке TRY и перебрасывает управление вместе с ошибкой в блок CATCH. В итоге мы получим вот такой код:

-- переменные для переброса данных об ошибке из CATCH в вызывающий код после отката изменений
DECLARE @errorMessage nvarchar(4000), @errorSeverity int
BEGIN TRY
  BEGIN TRANSACTION
    -- удаляем таблицу, копируем новую сервера, прописываем права
    DROP SOME_TABLE 
    -- если с другим сервером возникнет проблема, нужен откат
    SELECT  SOME_FIELD1, SOME_FIELD2, SOME_FIELD3 INTO SOME_TABLE FROM  OTHER_SERVER.OTHER_DATDABSE.dbo.OTHER_TABLE
    GRANT SELECT ON OBJECT::SOME_TABLE TO some_role; 
  -- если дошли до этой строки, все успешно, подтверждаем изменения
  COMMIT
END TRY
BEGIN CATCH
  -- COMMIT должен был уменьшить эту переменную до нуля, откатываем изменения 
  IF @@TRANCOUNT > 0 ROLLBACK
  -- выбрасываем новую ошибку в информацией из ошибки, пойманной оператором CATCH 
  SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY()
  RAISERROR(@errorMessage, @errorSeverity, 1)
END CATCH

Более подробно:

1. TRY…CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error
2. TRY…CATCH (Transact-SQL)
3. BEGIN TRANSACTION (Transact-SQL)

Использование TRY … CATCH в Transact-SQL — CodeProject

Введение

Использование TRY … CATCH в Transact-SQL

Цель этой статьи

Я расскажу о новой функции (TRY … CATCH) для обработки ошибок в SQL SERVER 2005

Microsoft представила Синтаксис TRY & CATCH (TAC) для обработки ошибок — это T-SQL, в SQL server 2005. .NET, должно быть, использовал этот синтаксис и раньше, поскольку этот TAC был первоначально представлен, а теперь они также вводят его в SQL.

Как это работает

Как я уже упоминал, этот блок TAC очень похож на то, что используется в других языках.net. Идея довольно проста

Когда условие ошибки обнаруживается в инструкции Transact-SQL, содержащейся в блоке TRY, управление передается блоку CATCH, где оно может быть обработано.

 Начать попробовать
 The_Query_for_which_we_need_to_do_the_ Error_Handling
Конец попытки
Начать ловлю
   
  Если в запросе в блоке Try есть ошибка, этот поток
  будет передан в этот блок Catch.End catch 

Если внутри блока TRY нет ошибок, управление передается оператору сразу после связанного оператора END CATCH. Если оператор END CATCH является последним оператором в хранимой процедуре или триггере, управление передается оператору, который вызвал хранимую процедуру или триггер.

Работают ли блоки TRY / CATCH при всех ошибках?

Нет, позаботьтесь о следующих моментах

• За блоком TRY должен сразу же следовать блок CATCH.
• Конструкции TRY… CATCH могут быть вложенными, что означает, что конструкции TRY… CATCH могут быть размещены внутри других блоков TRY и CATCH. Когда во вложенном блоке TRY возникает ошибка, управление программой передается блоку CATCH, связанному с вложенным блоком TRY.

 НАЧАТЬ ПОПРОБОВАТЬ
      Распечатайте "Я уровень 1"
          НАЧАТЬ ПОПРОБОВАТЬ
            Распечатайте "Я 2 уровень"
            ВЫБРАТЬ 1/0;
          КОНЕЦ ПОПЫТКИ
          НАЧАТЬ ЛОВ
   ВЫБРАТЬ
   ERROR_NUMBER () как номер ошибки,
   ERROR_SEVERITY () AS Уровень серьезности ошибок,
   ERROR_STATE () как ErrorState,
   ERROR_PROCEDURE () как ErrorProcedure,
   ERROR_LINE () как ErrorLine,
   ERROR_MESSAGE () как ErrorMessage;
    Распечатайте "Я выхожу на уровень 2"
         КОНЕЦ ЗАХВАТ
    Распечатайте "Я снова на уровне 1"
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
    
        ВЫБРАТЬ
        ERROR_NUMBER () как номер ошибки,
        ERROR_SEVERITY () AS Уровень серьезности ошибок,
        ERROR_STATE () как ErrorState,
        ERROR_PROCEDURE () как ErrorProcedure,
        ERROR_LINE () как ErrorLine,
        ERROR_MESSAGE () как ErrorMessage;
КОНЕЦ ЗАХВАТ 

Выход

 Я уровень 1
 Я уровень 2

(Затронуты 0 строк)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 NULL 5 Разделить на ноль
                                                            обнаружена ошибка.(Затронута 1 строка)
 Я выхожу уровень 2
 Я снова на уровне 1

В приведенном выше коде ошибка возникает из блока TAC уровня 2 

• Чтобы обработать ошибку, которая возникает в данном блоке CATCH, напишите блок TRY… … CATCH в указанном блоке CATCH.
• Ошибки с уровнем серьезности 20 или выше, вызывающие прерывание соединения компонентом Database Engine, не будут обрабатываться блоком TRY… CATCH. Однако TRY… CATCH будет обрабатывать ошибки с серьезностью 20 или выше, пока соединение не разорвано.
• Ошибки с уровнем серьезности 10 или ниже считаются предупреждениями или информационными сообщениями и не обрабатываются блоками TRY… CATCH.

Из двух приведенных выше утверждений ясно, что

Блок Try & Catch будет работать для

Ошибок с серьезностью 10

Функции ошибок

TRY … CATCH использует функции ошибок для сбора информации об ошибках.

ERROR_NUMBER () возвращает номер ошибки.

ERROR_MESSAGE () возвращает полный текст сообщения об ошибке. Текст включает значения, предоставленные для любых заменяемых параметров, таких как длина, имена объектов или время.

ERROR_SEVERITY () возвращает серьезность ошибки.

ERROR_STATE () возвращает номер состояния ошибки.

ERROR_LINE () возвращает номер строки внутри процедуры, вызвавшей ошибку.

ERROR_PROCEDURE () возвращает имя хранимой процедуры или триггера, в котором произошла ошибка.

Информация об ошибках извлекается с помощью этих функций из любого места в пределах области действия блока CATCH конструкции TRY… CATCH. Функции обработки ошибок вернут NULL, если они вызываются вне области действия блока CATCH. На функции ошибок можно ссылаться внутри хранимой процедуры и их можно использовать для получения информации об ошибках, когда хранимая процедура выполняется в блоке CATCH. При этом нет необходимости вводить код обработки ошибок в каждый блок CATCH.

Будут ли блоки TRY / CATCH перехватывать ошибки компиляции, такие как синтаксические ошибки, препятствующие выполнению пакета?


Нет, блок TAC не будет обнаруживать ошибки компиляции, если он не вызывается в исходном динамическом запросе или в каком-либо SP

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

 НАЧАТЬ ПОПРОБОВАТЬ
    
    
    ПЕЧАТЬ N'Начало выполнения '
   
   ВЫБРАТЬ * ИЗ NonExistentTable
    
    
      
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
    ВЫБРАТЬ
        ERROR_NUMBER () как номер ошибки,
        ERROR_MESSAGE () AS ErrorMessage;
КОНЕЦ ЗАХВАТ;
ИДТИ
Выход :
Начало исполнения
Сообщение 208, уровень 16, состояние 1, строка 6
Недопустимое имя объекта "NonExistentTable". 

Я постараюсь изложить вышеизложенную логику с помощью динамического SQL. При этом код будет компилироваться, но произойдет ошибка, которая будет обнаружена блоком TAC.

Динамический запрос

 НАЧАТЬ ПОПРОБОВАТЬ
    
    
    PRINT N'Начало выполнения ';
    ОБЪЯВИТЬ @SQL NVARCHAR (2000)
    SET @SQL = 'ВЫБРАТЬ * ИЗ NonExistentTable;'
    
    
   EXEC sp_executesql @SQL
      
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
    ВЫБРАТЬ
        ERROR_NUMBER () как номер ошибки,
        ERROR_MESSAGE () AS ErrorMessage;
КОНЕЦ ЗАХВАТ;
ИДТИ

Выход :
Начало исполнения
ErrorNumber ErrorMessage

208 Неверное имя объекта «NonExistentTable».(Затронуто 1 строка (строки)) 

Работа с транзакциями

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

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

Код в блоке CATCH должен проверять состояние транзакции с помощью функции XACT_STATE.XACT_STATE возвращает -1, если в сеансе есть незафиксированная транзакция. Блок CATCH не должен выполнять никаких действий, которые могут привести к записи в журнал, если XACT_STATE возвращает -1. Следующий пример кода генерирует ошибку из оператора DDL и использует XACT_STATE для проверки состояния транзакции, чтобы предпринять наиболее подходящее действие.

 СОЗДАТЬ ТЕМП. ТАБЛИЦЫ (A INT)
НАЧАТЬ ПОПРОБОВАТЬ
       
         ИЗМЕНИТЬ ТАБЛИЦУ ТЕМП.
            DROP COLUMN автор
         COMMIT TRAN
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
    
        ВЫБРАТЬ
        ERROR_NUMBER () как номер ошибки,
        ERROR_SEVERITY () AS Уровень серьезности ошибок,
        ERROR_STATE () как ErrorState,
        ERROR_PROCEDURE () как ErrorProcedure,
        ERROR_LINE () как ErrorLine,
        ERROR_MESSAGE () как ErrorMessage;
        PRINT '***** Значение XACT_STATE ****' + CONVERT (VARCHAR, XACT_STATE ())
КОНЕЦ ЗАХВАТ
ИДТИ
Выход
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage

3930 16 1 NULL 3 Текущая транзакция
                                                      не может быть совершено и
                                                      не может поддерживать
                                                      операции, которые пишут
                                                      в файл журнала.Рулон
                                                      вернуть транзакцию.
(Затронута 1 строка)
 ***** Значение XACT_STATE **** - 1 

.

TRY… CATCH на сервере Sql | SqlHints.com

Это вторая статья из серии статей по обработке исключений в Sql Server. Ниже приведен список других статей из этой серии.

Часть I: Основы обработки исключений — ДОЛЖЕН прочитать статью
Часть II: ПОПРОБОВАТЬ… CATCH (введено в Sql Server 2005)
Часть III: RAISERROR Vs THROW (Throw: введено в Sql Server 2012)
Часть IV: Шаблон обработки исключений

TRY… CATCH

TRY… CATCH — это конструкция обработки структурированных ошибок, представленная в Sql Server 2005.Он похож на тот, который у нас есть в C #, но в нем нет блока FINALLY. Если инструкция в блоке TRY вызывает какую-либо ошибку, управление немедленно передается в блок CATCH. Если ни один из операторов в блоке TRY не вызывает исключения, блок CATCH выполняться не будет.

СИНТАКСИС:

 НАЧАТЬ ПОПРОБОВАТЬ
 - Заявления T-Sql
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
 - Заявления T-Sql
 / * Управление передается блоку CATCH только если
   в блоке TRY есть исключения * /
КОНЦЕВОЙ ЗАХВАТ 

Давайте разберемся с конструкцией TRY… CATCH с подробным списком примеров ниже.

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

 --Создайте новую базу данных для демонстрации
СОЗДАТЬ БАЗУ ДАННЫХ SqlHintsErrorHandlingDemo
ИДТИ
ИСПОЛЬЗОВАТЬ SqlHintsErrorHandlingDemo
ИДТИ
СОЗДАТЬ ТАБЛИЦУ dbo.Account
(
 AccountId INT NOT NULL PRIMARY KEY,
 Имя NVARCHAR (50) NOT NULL,
 Деньги на балансе НЕ ПРОВЕРКА NULL (Баланс> = 0)
)
GO 

Так как таблица Account имеет первичный ключ в столбце AccountId, это вызовет ошибку, если мы попытаемся дублировать значение столбца AccountId.В столбце Balance есть ограничение CHECK Balance> = 0, поэтому оно вызовет исключение, если значение Balance <0.

ДЕМО 1:

Давайте выполним приведенный ниже сценарий и посмотрим, как будет вести себя конструкция TRY..CATCH, если ни одно из операторов в блоке TRY не вызовет исключения.

 ПЕЧАТЬ "ПЕРЕД ПОПЫТОЙ"
НАЧАТЬ ПОПРОБОВАТЬ
 ПЕЧАТЬ 'Первая инструкция в блоке TRY'
 ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance)
 ЗНАЧЕНИЯ (1; 'Account1'; 10000)
 ПЕЧАТЬ 'Последний оператор в блоке TRY'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
ПЕЧАТЬ 'В ЗАМЕТКЕ'
КОНЕЦ ЗАХВАТ
ПЕЧАТЬ 'After END CATCH'
GO 

РЕЗУЛЬТАТ:

Из приведенного выше результата ясно, что, если операторы, заключенные в блоке TRY, не приводят к каким-либо ошибкам, тогда управление не передается блоку CATCH, вместо этого выполнение продолжается сразу после оператора END CATCH.

Давайте очистим таблицу счетов перед переходом к следующей ДЕМО.

 УДАЛИТЬ ИЗ dbo.Account
GO 

ДЕМО 2:

Давайте выполним приведенный ниже сценарий и посмотрим, как будет себя вести конструкция TRY..CATCH. Здесь оператор Second INSERT приводит к ошибке оператора Terminating Primary Key Violation.

 ПЕЧАТЬ "ПЕРЕД ПОПЫТОЙ"
НАЧАТЬ ПОПРОБОВАТЬ
 ПЕЧАТЬ 'Первая инструкция в блоке TRY'
 ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance)
 ЗНАЧЕНИЯ (1, 'Account1', 10000)
 ВСТАВИТЬ dbo.Счет (AccountId, имя, баланс)
 ЗНАЧЕНИЯ (1, "Дубликат", 10000)
 ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance)
 ЗНАЧЕНИЯ (2, 'Account2', 20000)
 ПЕЧАТЬ 'Последний оператор в блоке TRY'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
ПЕЧАТЬ 'В ЗАМЕТКЕ'
КОНЕЦ ЗАХВАТ
ПЕЧАТЬ 'After END CATCH'
ВЫБРАТЬ * ИЗ dbo.Account WITH (NOLOCK)
GO 

РЕЗУЛЬТАТ:

Из приведенного выше результата ясно, что как только ошибка обнаруживается в блоке TRY, sql server завершает оператор, вызвавший ошибку, никакие последующие операторы в блоке TRY не выполняются, и управление передается блоку CATCH.

Давайте очистим таблицу счетов перед переходом к следующей ДЕМО.

 УДАЛИТЬ ИЗ dbo.Account
GO 

ДЕМО 3:

Давайте выполним приведенный ниже сценарий и посмотрим, как будет себя вести конструкция TRY..CATCH. Здесь второй оператор (т.е. оператор UPDATE) приводит к ошибке Batch Abortion CONVERSION / CAST.

 ПЕЧАТЬ "ПЕРЕД ПОПЫТОЙ"
НАЧАТЬ ПОПРОБОВАТЬ
 ПЕЧАТЬ 'Первая инструкция в блоке TRY'
 ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance)
 ЗНАЧЕНИЯ (1, 'Account1', 10000)

 ОБНОВЛЕНИЕ dbo.Счет
 УСТАНОВИТЬ Баланс = Баланс + КАСТ (ДЕСЯТЬ ТЫСЯЧ В ДЕНЬГИ)
 ГДЕ AccountId = 1

 ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance)
 ЗНАЧЕНИЯ (2, 'Account2', 20000)
 ПЕЧАТЬ 'Последний оператор в блоке TRY'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
ПЕЧАТЬ 'В ЗАМЕТКЕ'
КОНЕЦ ЗАХВАТ
ПЕЧАТЬ 'After END CATCH'
ВЫБРАТЬ * ИЗ dbo.Account WITH (NOLOCK)
GO 

РЕЗУЛЬТАТ:

Из приведенного выше результата ясно, что как только ошибка обнаруживается в блоке TRY, sql server завершает оператор, вызвавший ошибку, никакие последующие операторы в блоке TRY не выполняются, и управление передается блоку CATCH.

Давайте очистим таблицу счетов перед переходом к следующей ДЕМО.

 УДАЛИТЬ ИЗ dbo.Account
GO 

ДЕМО 4:

В этой ДЕМО мы увидим, как мы можем использовать транзакции с конструкциями TRY..CATCH.

 ПЕЧАТЬ "ПЕРЕД ПОПЫТОЙ"
НАЧАТЬ ПОПРОБОВАТЬ
НАЧАТЬ ТРАН
ПЕЧАТЬ 'Первая инструкция в блоке TRY'
ВСТАВИТЬ В dbo.Account (AccountId, Name, Balance) VALUES (1, 'Account1', 10000)
ОБНОВЛЕНИЕ dbo.Account SET Balance = Balance + CAST ('ДЕСЯТЬ ТЫСЯЧ' КАК ДЕНЬГИ) ГДЕ AccountId = 1
ВСТАВИТЬ dbo.Счет (AccountId, имя, баланс) ЗНАЧЕНИЯ (2, 'Account2', 20000)
ПЕЧАТЬ 'Последний оператор в блоке TRY'
COMMIT TRAN
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
    ПЕЧАТЬ 'В ЗАМЕТКЕ'
ЕСЛИ (@@ TRANCOUNT> 0)
ROLLBACK TRAN
КОНЕЦ ЗАХВАТ
ПЕЧАТЬ 'After END CATCH'
ВЫБРАТЬ * ИЗ dbo.Account WITH (NOLOCK)
GO 

РЕЗУЛЬТАТ:

Здесь @@ TRANCOUNT в блоке CATCH указывает, есть ли у нас активные транзакции, если да, мы откатываем транзакцию.По этой причине мы не видим никаких записей в таблице счетов.

ДЕМО 5:

В этой демонстрации мы увидим набор из ФУНКЦИЙ ОШИБОК , которые мы можем использовать в блоке CATCH, чтобы получить подробную информацию об ошибке, которая привела к передаче управления из блока TRY в блок CATCH

.

 НАЧАТЬ ПОПРОБОВАТЬ
 ВЫБРАТЬ 5/0
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
 ПЕЧАТЬ '************* Сведения об ошибке ****************'
 ПЕЧАТЬ 'Номер ошибки:' + CAST (ERROR_NUMBER () AS VARCHAR)
 PRINT 'Серьезность ошибки:' + CAST (ERROR_SEVERITY () AS VARCHAR)
 PRINT 'Состояние ошибки:' + CAST (ERROR_STATE () AS VARCHAR)
 ПЕЧАТЬ 'Строка ошибки:' + CAST (ERROR_LINE () AS VARCHAR)
 PRINT 'Сообщение об ошибке:' + ERROR_MESSAGE ()
КОНЦЕВОЙ ЗАХВАТ 

РЕЗУЛЬТАТ:

ФУНКЦИИ ОШИБОК на сервере Sql

Ниже приведен список ФУНКЦИЙ ОШИБОК, которые мы можем использовать в блоке CATCH, чтобы получить подробную информацию об ошибке, которая привела к передаче управления из блока TRY в блок CATCH.

• ERROR_NUMBER (): возвращает номер ошибки.
• ERROR_SEVERITY (): возвращает серьезность ошибки.
• ERROR_STATE (): возвращает состояние ошибки.
• ERROR_PROCEDURE (): возвращает имя SP / UDF, в котором произошла ошибка.
• ERROR_LINE (): возвращает номер строки оператора Sql, вызвавшего ошибку.
• ERROR_MESSAGE (): возвращает сообщение об ошибке.

Эта функция может использоваться только в рамках блока CATCH.Они не будут возвращать значение вне блока CATCH, пример ниже демонстрирует это:

SELECT 5/0
SELECT ERROR_NUMBER () «Номер ошибки», ERROR_MESSAGE () «Сообщение об ошибке»

РЕЗУЛЬТАТ:

ДЕМО 6:

TRY… CATCH перехватывает все ошибки, кроме ошибок завершения соединения. Это означает, что любая ошибка в блоке TRY с уровнем серьезности между 11-19 заставляет Sql Server передать управление блоку CATCH. Единственным исключением является ошибка Scope Abortion (т.е. Отложенное разрешение имени), что не является ошибкой разрыва соединения, но не может быть перехвачено конструкцией TRY… CATCH. Пример ниже демонстрирует это:

 ПЕЧАТЬ "ПЕРЕД ПОПЫТОЙ"
НАЧАТЬ ПОПРОБОВАТЬ
  ПЕЧАТЬ 'Первая инструкция в блоке TRY'
  ВЫБРАТЬ * ИЗ NONExistentTable
  ПЕЧАТЬ 'Последний оператор в блоке TRY'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
  ПЕЧАТЬ 'В блоке CATCH'
КОНЕЦ ЗАХВАТ
ПЕЧАТЬ 'After END CATCH'
GO 

РЕЗУЛЬТАТ:

Будет рассмотрено еще несколько примеров конструкции TRY… CATCH при обсуждении заявлений RAISERROR и THROW.

.Сервер

sql — Sql Try Catch с циклом while

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

Обработка исключений в SQL Server с помощью TRY… CATCH

Подобно C #, SQL Server также имеет модель исключений для обработки исключений и ошибок, возникающих в операторах T-SQL. Для обработки исключений в Sql Server у нас есть блоки TRY..CATCH. Мы помещаем операторы T-SQL в блок TRY, а для обработки исключения пишем код в блоке CATCH. Если в блоке TRY есть ошибка в коде, управление автоматически перейдет к соответствующим блокам CATCH. В Sql Server против блока Try у нас может быть только один блок CATCH.

TRY..CATCH Синтаксис

 BEGIN TRY
Операторы --T-SQL
- или блоки операторов T-SQL
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
Операторы --T-SQL
- или блоки операторов T-SQL
END CATCH 

Функции ошибок, используемые в блоке CATCH

1. ERROR_NUMBER ()

   Это возвращает номер ошибки, и его значение такое же, как для функции @@ ERROR.

2. ERROR_LINE ()

   Возвращает номер строки оператора T-SQL, вызвавшего ошибку.

3. ERROR_SEVERITY ()

   Возвращает уровень серьезности ошибки.

4. ERROR_STATE ()

   Возвращает номер состояния ошибки.

5. ERROR_PROCEDURE ()

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

6. ERROR_MESSAGE ()

   Возвращает полный текст сообщения об ошибке. Текст включает значения, предоставленные для любых заменяемых параметров, таких как длина, имена объектов или время.

Пример обработки исключительной ситуации

 BEGIN TRY
ОБЪЯВЛЕНИЕ @num INT, @msg varchar (200)
---- Разделите на ноль, чтобы сгенерировать ошибку
УСТАНОВИТЬ @num = 5/0
ПЕЧАТЬ 'Это не будет выполнено'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
PRINT 'Произошла ошибка, то есть'
установить @ msg = (ВЫБРАТЬ ERROR_MESSAGE ())
print @msg;
КОНЕЦ ЗАХВАТ
GO 

 НАЧАТЬ ПОПРОБОВАТЬ
ОБЪЯВИТЬ @num INT
---- Разделите на ноль, чтобы сгенерировать ошибку
УСТАНОВИТЬ @num = 5/0
ПЕЧАТЬ 'Это не будет выполнено'
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВ
SELECT ERROR_NUMBER () AS ErrorNumber, ERROR_SEVERITY () AS ErrorSeverity, ERROR_STATE () AS ErrorState, ERROR_PROCEDURE () как ErrorProcedure, ERROR_LINE () AS ErrorLine, ERROR_MESSAGE () AS ErrorMessage;
КОНЕЦ ЗАХВАТ;
GO 

Примечание

1. A TRY.Комбинация блоков .CATCH улавливает все ошибки с уровнем серьезности от 11 до 19.

2. Блок CATCH выполняется только в том случае, если в операторах T-SQL в блоке TRY возникает ошибка, в противном случае блок CATCH игнорируется.

3. Каждый блок TRY связан только с одним блоком CATCH, и наоборот

4. Блоки TRY и CATCH не могут быть разделены с помощью оператора GO. Нам нужно поместить блоки TRY и CATCH в один пакет.

5. Блоки TRY..CATCH могут использоваться с транзакциями. Мы проверяем количество открытых транзакций с помощью функции @@ TRANCOUNT в Sql Server.

6. Функция XACT_STATE в блоке TRY..CATCH может использоваться для проверки того, зафиксирована ли открытая транзакция или нет. Он вернет -1, если транзакция не зафиксирована, иначе вернет 1.

Резюме

В этой статье я пытаюсь объяснить обработку исключений в Sql Server на примере.Я надеюсь, что после прочтения этой статьи вы будете знать, как обрабатывать исключения в Sql Server. Я хотел бы получить отзывы от читателей моего блога. Пожалуйста, оставьте свой отзыв, вопрос или комментарий об этой статье.

Поделиться Артикул

Пройдите бесплатные тесты, чтобы оценить свои навыки!

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

.

No related posts.