| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
TRY…CATCH (Transact-SQL) |
TRY…CATCH (Transact-SQL) |
rwestMSFT |
randolphwest |
03/16/2017 |
sql |
t-sql |
reference |
|
|
TSQL |
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
Implements error handling for [!INCLUDEtsql] that is similar to the exception handling in the [!INCLUDEmsCoName] Visual C# and [!INCLUDEmsCoName] Visual C++ languages. A group of [!INCLUDEtsql] statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is usually passed to another group of statements that is enclosed in a CATCH block.
:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions
Syntax
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]
[!INCLUDEsql-server-tsql-previous-offline-documentation]
Arguments
sql_statement
Is any [!INCLUDEtsql] statement.
statement_block
Any group of [!INCLUDEtsql] statements in a batch or enclosed in a BEGIN…END block.
Remarks
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of [!INCLUDEtsql] statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of [!INCLUDEtsql] statements and cannot span an IF…ELSE construct.
If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement.
If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement.
[!NOTE]
If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.
Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.
TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs. For example, a CATCH block can contain an embedded TRY…CATCH construct to handle errors encountered by the CATCH code.
Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY…CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. If there is no nested TRY…CATCH construct, the error is passed back to the caller.
TRY…CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. Alternatively, the stored procedures or triggers can contain their own TRY…CATCH constructs to handle errors generated by their code. For example, when a TRY block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:
-
If the stored procedure does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.
-
If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.
GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.
The TRY…CATCH construct cannot be used in a user-defined function.
Retrieving Error Information
In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:
-
ERROR_NUMBER() returns the number of the error.
-
ERROR_SEVERITY() returns the severity.
-
ERROR_STATE() returns the error state number.
-
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
-
ERROR_LINE() returns the line number inside the routine that caused the error.
-
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
These functions return NULL if they are called outside the scope of the CATCH block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH block. For example, the following script shows a stored procedure that contains error-handling functions. In the CATCH block of a TRY...CATCH construct, the stored procedure is called and information about the error is returned.
-- Verify that the stored procedure does not already exist. IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL DROP PROCEDURE usp_GetErrorInfo; GO -- Create procedure to retrieve error information. CREATE PROCEDURE usp_GetErrorInfo AS 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; GO BEGIN TRY -- Generate divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- Execute error retrieval routine. EXECUTE usp_GetErrorInfo; END CATCH;
The ERROR_* functions also work in a CATCH block inside a natively compiled stored procedure.
Errors Unaffected by a TRY…CATCH Construct
TRY…CATCH constructs do not trap the following conditions:
-
Warnings or informational messages that have a severity of 10 or lower.
-
Errors that have a severity of 20 or higher that stop the [!INCLUDEssDEnoversion] task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
-
Attentions, such as client-interrupt requests or broken client connections.
-
When the session is ended by a system administrator by using the KILL statement.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
-
Compile errors, such as syntax errors, that prevent a batch from running.
-
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
-
Object name resolution errors
These errors are returned to the level that ran the batch, stored procedure, or trigger.
If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.
The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY...CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.
BEGIN TRY -- Table does not exist; object name resolution -- error not caught. SELECT * FROM NonexistentTable; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; END CATCH
The error is not caught and control passes out of the TRY...CATCH construct to the next higher level.
Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY...CATCH construct.
-- Verify that the stored procedure does not exist. IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL DROP PROCEDURE usp_ExampleProc; GO -- Create a stored procedure that will cause an -- object resolution error. CREATE PROCEDURE usp_ExampleProc AS SELECT * FROM NonexistentTable; GO BEGIN TRY EXECUTE usp_ExampleProc; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; END CATCH;
Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any [!INCLUDEtsql] statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the [!INCLUDEssDE] rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.
For more information about uncommittable transactions and the XACT_STATE function, see XACT_STATE (Transact-SQL).
Examples
A. Using TRY…CATCH
The following example shows a SELECT statement that will generate a divide-by-zero error. The error causes execution to jump to the associated CATCH block.
BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH 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 CATCH; GO
B. Using TRY…CATCH in a transaction
The following example shows how a TRY...CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.
BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH 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; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO
C. Using TRY…CATCH with XACT_STATE
The following example shows how to use the TRY...CATCH construct to handle errors that occur inside a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT is ON. This makes the transaction uncommittable when the constraint violation error occurs.
-- Check to see whether this stored procedure exists. IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL DROP PROCEDURE usp_GetErrorInfo; GO -- Create procedure to retrieve error information. CREATE PROCEDURE usp_GetErrorInfo AS SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_LINE () AS ErrorLine ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_MESSAGE() AS ErrorMessage; GO -- SET XACT_ABORT ON will cause the transaction to be uncommittable -- when the constraint violation occurs. SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- A FOREIGN KEY constraint exists on this table. This -- statement will generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; -- If the DELETE statement succeeds, commit the transaction. COMMIT TRANSACTION; END TRY BEGIN CATCH -- Execute error retrieval routine. EXECUTE usp_GetErrorInfo; -- Test XACT_STATE: -- If 1, the transaction is committable. -- If -1, the transaction is uncommittable and should -- be rolled back. -- XACT_STATE = 0 means that there is no transaction and -- a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT N'The transaction is in an uncommittable state.' + 'Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether the transaction is committable. -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error. IF (XACT_STATE()) = 1 BEGIN PRINT N'The transaction is committable.' + 'Committing transaction.' COMMIT TRANSACTION; END; END CATCH; GO
D. Using TRY…CATCH
The following example shows a SELECT statement that will generate a divide-by-zero error. The error causes execution to jump to the associated CATCH block.
BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO
See Also
THROW (Transact-SQL)
Database Engine Error Severities
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL)
GOTO (Transact-SQL)
BEGIN…END (Transact-SQL)
XACT_STATE (Transact-SQL)
SET XACT_ABORT (Transact-SQL)
Обработка ошибок
Последнее обновление: 14.08.2017
Для обработки ошибок в T-SQL применяется конструкция TRY…CATCH. Она имеет следующий формальный синтаксис:
BEGIN TRY инструкции END TRY BEGIN CATCH инструкции END CATCH
Между выражениями BEGIN TRY и END TRY помещаются инструкции, которые потенциально могут вызвать ошибку, например,
какой-нибудь запрос. И если в этом блоке TRY возникнет ошибка, то управление передается в блок CATCH, где можно обработать ошибку.
В блоке CATCH для обаботки ошибки мы можем использовать ряд функций:
-
ERROR_NUMBER(): возвращает номер ошибки
-
ERROR_MESSAGE(): возвращает сообщение об ошибке
-
ERROR_SEVERITY(): возвращает степень серьезности ошибки. Степень серьезности представляет числовое значение. И если оно равно 10 и меньше, то
такая ошибка рассматривается как предупреждение и не обрабатывается конструкцией TRY…CATCH. Если же это значение равно 20 и выше, то
такая ошибка приводит к закрытию подключения к базе данных, если она не обрабатывается конструкцией TRY…CATCH. -
ERROR_STATE(): возвращает состояние ошибки
Например, добавим в таблицу данные, которые не соответствуют ограничениям столбцов:
CREATE TABLE Accounts (FirstName NVARCHAR NOT NULL, Age INT NOT NULL) BEGIN TRY INSERT INTO Accounts VALUES(NULL, NULL) PRINT 'Данные успешно добавлены!' END TRY BEGIN CATCH PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' + ERROR_MESSAGE() END CATCH
В данном случае для столбцов таблицы вставляются недопустимые данные — значения NULL, поэтому обработка программы перейдет к блоку CATCH:
В языке T-SQL, как и во многих других языках программирования, есть возможность отслеживать и перехватывать ошибки, сегодня мы с Вами рассмотрим конструкцию TRY CATCH, с помощью которой мы как раз и можем обрабатывать исключительные ситуации, т.е. непредвиденные ошибки.
Как Вы уже поняли, речь здесь пойдет не о синтаксических ошибках, о которых нам сообщает SQL сервер еще до начала выполнения самих SQL инструкций, а об ошибках, которые могут возникнуть на том или ином участке кода при определенных условиях.
Самый простой пример — это деление на ноль, как Вы знаете, делить на ноль нельзя, но эта цифра все-таки может возникнуть в операциях деления. Также существуют и другие ошибки, которые могут возникнуть в операциях над нестандартными, некорректными данными, хотя те же самые операции с обычными данными выполняются без каких-либо ошибок.
Поэтому в языке Transact-SQL существует специальная конструкция TRY…CATCH, она появилась в 2005 версии SQL сервера, и которая используется для обработки ошибок. Если кто знаком с другими языками программирования, то Вам эта конструкция скорей всего знакома, так как она используется во многих языках программирования.
Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.
Содержание
- Конструкция TRY CATCH в T-SQL
- Важные моменты про конструкцию TRY CATCH в T-SQL
- Функции для получения сведений об ошибках
- Пример использования конструкции TRY…CATCH для обработки ошибок
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
- 2
- 3
- вперед »
TRY/CATCH, XACT_STATE, XACT_ABORT и транзакции.
Мы уже обсудили довольно много аспектов перехвата ошибок, но не коснулись аспекта, возможно, главного: а как вся эта «музыка» работает с такой вещью как транзакция? Ведь наши «потенциально опасные» инструкции исполняются обычно в рамках как-раз таки транзакций! Если ответить на поставленный вопрос коротко, то это будет «все работает». Но сказать что «все работает и нет никаких нюансов» было бы непростительной самонадеянностью, как раз к нюансам взаимодействия блоков перехвата и транзакций давайте переходить.
Стало быть, прежде всего что следует себе уяснить так это полную «отвязанность» двух конструкций друг от друга. Никто не «главный» и никто не «подчиненный». Никто не обязан быть блоком «объемлющим» и никто не обязан быть блоком «вложенным». Вполне может быть одно и не быть другого. Однако если все же есть и первое, и второе, то возникают вполне четко выраженные обязательства по отношению друг к другу. Как у нас вообще могут «сойтись» в одной точке кода и транзакции, и блоки перехвата и обработки? Очевидно — что-то во что-то вкладывается, как же еще? Не менее очевидно что способов вложения всего два. Или «TRY в транзакции»:
|
1 |
BEGIN TRAN |
Или «транзакция в TRY»:
|
1 |
BEGIN TRY |
Как было отмечено выше, в связи с полным отсутствием иерархических взаимоотношений между разбираемыми синтаксическими элементами нет и не может быть вложения «правильного» и «ошибочного». Оба показанных варианта функционально эквивалентны. Выбирайте любой исходя из эстетических своих предпочтений. Если вам это интересно, лично автору блога на котором вы в настоящий момент находитесь ближе и «родней» вариант номер 2. Он предпочитает что бы все вещи относящиеся к одной транзакции, включая и открытие оной, происходили в одном «месте», блоке. Однако, повторю, выбор между двумя показанными подходами — вопрос стиля, а не технической корректности кода.
Хорошо, вот эти две вещи соединились — какое главное правило такого «слияния»? Очень простое: если в блоке TRY у вас есть «ваша» открытая транзакция (открыли ли вы ее методом 1, или методом 2 — не важно), то совершенно все (без исключения!) пути исполнения вашего кода как в блоке TRY, так и в блоке CATCH обязаны вести к завершению транзакции хоть фиксацией («коммит»), хоть откатом («ролбэк»). Худшее что вы можете сделать — покинуть блок TRY/CATCH оставив транзакцию в «подвешенном» состоянии. Ну а каков план реализации этого «очень простого правила»? А вот тут и начинаются обещанные нюансы…
План «генеральный», в общем-то, уже показан в двух отрывках кода выше. Если блок TRY пройден почти до конца, то перед самой меткой END TRY мы транзакцию фиксируем — ведь мы не испытали никаких проблем при ее исполнении, верно? Конечно, никто не может нам запретить из анализа некоторой информации транзакцию в той же точке откатить, но чаще — фиксируем. Ну а если мы «свалились» в блок CATCH, то сразу же за меткой BEGIN CATCH (или, по крайней мере, недалеко от нее) мы транзакцию откатываем. А есть ли тут у нас свобода выбора безусловно присутствующая в блоке предыдущем? Можем ли мы находясь в блоке CATCH транзакцию все же зафиксировать? It, как говорится, depends. Давайте к коду:
|
1 |
USE tempdb |
Результаты:
и
Транзакция открыта и находится в фиксируемом состоянии, начинаю фиксацию…
Стало быть, один факт установлен доподлинно — есть варианты! И даже в блоке CATCH. Но, во-первых, обратите внимание, что из двух легальных значений колонки ID зафиксировалось (да и было вставлено, на самом деле) лишь первое. Ведь уходя в CATCH мы в TRY не возвращаемся, не забыли? А, во-вторых, обращают на себя внимание две новых синтаксических конструкции: XACT_ABORT до начала транзакции и XACT_STATE в блоке CATCH. «Разбор полетов» начнем с последней.
Функция XACT_STATE сообщает нам о состоянии текущей транзакции «внутри» которой мы находимся в момент вызова этой функции. XACT_STATE возвращает нам целое число и причем варианта всего три:
- 0 — никакой транзакции вообще нет, не о чем и беспокоиться в плане ее корректного завершения;
- 1 — активная транзакция есть и находится в состоянии допускающим как ее откат, так и ее фиксацию, выбор за нами;
- -1 — тоже активная транзакция есть, но она перешла в специальное «нефиксируемое» состояние. Единственно возможная операция с такой транзакцией — полный и безусловный ее откат. Который, тем не менее, не выполняется движком сервера в автоматическом режиме. И указание инструкции ROLLBACK остается прерогативой и обязанностью (причем одной из главнейших) нашего T-SQL программиста.
Ну с первым значением вопросов нет, отсутствует транзакция — так и нам спокойнее. А вот с двумя вторыми значениями? Отчего бывает так, а бывает и эдак? А это зависит от серьезности той ошибки что привела нас в блок CATCH. Если ошибка «жесткая» мы получаем -1, если «мягкая» — +1. Кстати говоря, если та же самая транзакция будет исполняться вне блока TRY/CATCH то «жесткая» ошибка приведет к полной отмене всей транзакции, со всеми инструкциями ее составляющими. А ошибка «мягкая» приведет к выкидыванию из состава транзакции лишь «плохой» инструкции, остальные инструкции той же транзакции будут продолжены. Скажем исполнение вот такого кода:
|
1 |
BEGIN TRANSACTION |
приведет к вставке двух строк и плюс к предупреждению
Cannot insert the value NULL into column ‘id’, table ‘tempdb.dbo.T1’; column does not allow nulls. INSERT fails.
И это все потому, что вставка нелегального значения — ошибка «мягкая». Измените строчку ее вызывающую, т.е.
на
|
1 |
ALTER TABLE T1 DROP CONSTRAINT NonExist |
и ни одна строка вставлена не будет, подавляющее число ошибок команд группы DDL — «жесткие». И, как вы правильно понимаете, если мы исправленный вариант нашей транзакции вновь «обернем» в TRY/CATCH, то сообщение будет
Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…
и уж конечно ни одна из вставляемых строк в таблице T1 обнаружена не будет. Так что разница между 1 и -1 возвращаемыми нам функцией XACT_STATE сводится к «жесткости» ошибки.
Хорошо, а роль второй инструкции, XACT_ABORT какова? А вот какова. Если значение этой опции (как и все прочие «SET-опции» она устанавливается для текущей сессии соединения с сервером) OFF — то «мягкие» ошибки будут «мягкими». А «жесткие», как легко догадаться, «жесткими». Кстати, каждое новое подключение начинает свою работу именно с этого значения обсуждаемой опции. А вот переводя эту опцию в значение противоположное мы заявляем, что хотим считать все ошибки — «жесткими». Ну и конечно хотим что бы движок сервера разделял эту нашу точку зрения. Например измените в скрипте чуть выше, где эта опция упомянута, ее значение на ON. В остальном оставьте скрипт как есть. Вы помните, что в предыдущий раз у нас одна строка вставилась в таблицу T1 и эта вставка была успешно зафиксирована. А что теперь?
Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…
и полное отсутствие записей в таблице T1.
Ну а какое значение опции XACT_ABORT «правильное»? Под каким лучше работать? А вот это — хороший вопрос если вы планируете начать новую «священную войну» на техническом форуме. Скажем так: если бы «хорошесть» того или другого варианта была доказана неопровержимо и на 120%, то команде разработчиков, надо думать, не составило бы труда прописать это «победившее» значение в код движка, а опцию XACT_ABORT просто убрать с глаз долой. Ан нет — не прописывают и не убирают. Апологеты обоих подходов приводят свои доказательства на тему «как надо жить», однако «финальный довод» пока не дался никому. Автор данных строк принадлежит лагерю сторонников опции ON и вот почему. Во-первых, при работе с распределенными запросами и распределенными транзакциями данное значение должно быть выставлено для XACT_ABORT без всяких дискуссий, просто потому что так предписывает BOL. Но распределенные запросы — случай частный и специфичный, так что аргумент получается хоть и неопровержимый, но уж очень «узконаправленный», не масштабный. Однако есть и более весомое во-вторых. Вот код:
|
1 |
USE tempdb |
Запустите показанный код в студии и пока он «вращается» в заданной 10-ти секундной задержке прервите его выполнение нажав кнопку Cancel Executing Query на панели студии либо выбрав одноименный пункт в меню Query. Если бы наш клиент предполагал выход из запроса по слишком долгому времени потраченному на его исполнение (query timeout) то можно было бы и выждать данный отрезок времени не «снося» запрос принудительно — все дальнейшие замечания не потеря ли бы своей актуальности. Так вот, отменив запрос мы видим что блок CATCH не отработал. И это разумеется, и это ожидаемо, ведь выполнение кода отменено, не правда ли? А вот о чем многие не подозревают, так это то, что транзакция осталась открытой. В чем легко убедиться открыв в редакторе студии еще одну вкладку и выполнив там
|
1 |
SELECT * FROM sys.dm_tran_active_transactions WHERE transaction_type=1 |
И будет она, транзакция наша, так и «висеть», пока клиент не удосужится сделать ей принудительный откат. Но «висит-то» она не просто «висит»: она сохраняет все свои блокировки, на ее поддержку тратятся ресурсы и т.д. Да, если клиент «отвалится» (закроет соединение) то требуемый откат совершит сам движок, без участия клиента. Однако тут есть большая «засада», помимо очевидной, что клиент еще должен «отвалиться» и не факт что это случится спустя микросекунду после отмены им «долгоиграющего» запроса. «Засада» заключается в том, что большинство современных платформ создания приложений пользователя (а энтузиасты ваявшие софт на чистом ассемблере давно перевелись) используют парадигму «пула подключений» (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) для оформления своих хранимых процедур со всеми «обвесами» по умолчанию. В качестве «точки старта» можете воспользоваться шаблоном автора:
|
1 |
— ============================================= DECLARE @localTran bit BEGIN TRY —CODE GOES HERE IF @localTran = 1 AND XACT_STATE() = 1 END TRY DECLARE @ErrorMessage NVARCHAR(4000) SELECT @ErrorMessage = ERROR_MESSAGE(), IF @localTran = 1 AND XACT_STATE() <> 0 RAISERROR (‘**From %s**: ErrNum=%d:ErrSt=%d:ErrMsg=»%s»‘, 16, 1, @ErrorProc, @ErrorNum, @ErrorState, @ErrorMessage) END |
Разумеется, создать шаблон годящийся для 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 |
DECLARE @a int, @b int |
Смотреть ответ для Скрипт 1
PRINT: срабатывает
SELECT: 1,2,4,5
Скрипт 2
|
1 |
DECLARE @a int, @b int |
Смотреть ответ для Скрипт 2
PRINT: срабатывает
SELECT: 1,2,4,5
Скрипт 3
|
1 |
DECLARE @a int, @b int |
Смотреть ответ для Скрипт 3
PRINT: срабатывает
SELECT: 4,5
Скрипт 4
|
1 |
DECLARE @a int, @b int |
Смотреть ответ для Скрипт 4
PRINT: срабатывает
SELECT: 1,2
Скрипт 5
|
1 |
DECLARE @a int, @b int BEGIN |
Смотреть ответ для Скрипт 5
PRINT: срабатывает
SELECT: 1,2
Скрипт 6
|
1 |
DECLARE @a int, @b int |
Смотреть ответ для Скрипт 6
PRINT: НЕ срабатывает
SELECT: пустой резалт-сет
- Другие части статьи:
- 1
- 2
- 3
- вперед »
Summary: in this tutorial, you will learn how to use the SQL Server TRY CATCH construct to handle exceptions in stored procedures.
SQL Server TRY CATCH overview
The TRY CATCH construct allows you to gracefully handle exceptions in SQL Server. To use the TRY CATCH construct, you first place a group of Transact-SQL statements that could cause an exception in a BEGIN TRY...END TRY block as follows:
Code language: SQL (Structured Query Language) (sql)
BEGIN TRY -- statements that may cause exceptions END TRY
Then you use a BEGIN CATCH...END CATCH block immediately after the TRY block:
BEGIN CATCH -- statements that handle exception END CATCHCode language: SQL (Structured Query Language) (sql)
The following illustrates a complete TRY CATCH construct:
Code language: SQL (Structured Query Language) (sql)
BEGIN TRY -- statements that may cause exceptions END TRY BEGIN CATCH -- statements that handle exception END CATCH
If the statements between the TRY block complete without an error, the statements between the CATCH block will not execute. However, if any statement inside the TRY block causes an exception, the control transfers to the statements in the CATCH block.
The CATCH block functions
Inside the CATCH block, you can use the following functions to get the detailed information on the error that occurred:
ERROR_LINE()returns the line number on which the exception occurred.ERROR_MESSAGE()returns the complete text of the generated error message.ERROR_PROCEDURE()returns the name of the stored procedure or trigger where the error occurred.ERROR_NUMBER()returns the number of the error that occurred.ERROR_SEVERITY()returns the severity level of the error that occurred.ERROR_STATE()returns the state number of the error that occurred.
Note that you only use these functions in the CATCH block. If you use them outside of the CATCH block, all of these functions will return NULL.
Nested TRY CATCH constructs
You can nest TRY CATCH construct inside another TRY CATCH construct. However, either a TRY block or a CATCH block can contain a nested TRY CATCH, for example:
Code language: SQL (Structured Query Language) (sql)
BEGIN TRY --- statements that may cause exceptions END TRY BEGIN CATCH -- statements to handle exception BEGIN TRY --- nested TRY block END TRY BEGIN CATCH --- nested CATCH block END CATCH END CATCH
SQL Server TRY CATCH examples
First, create a stored procedure named usp_divide that divides two numbers:
Code language: SQL (Structured Query Language) (sql)
CREATE PROC usp_divide( @a decimal, @b decimal, @c decimal output ) AS BEGIN BEGIN TRY SET @c = @a / @b; END TRY BEGIN CATCH 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 CATCH END; GO
In this stored procedure, we placed the formula inside the TRY block and called the CATCH block functions ERROR_* inside the CATCH block.
Second, call the usp_divide stored procedure to divide 10 by 2:
Code language: SQL (Structured Query Language) (sql)
DECLARE @r decimal; EXEC usp_divide 10, 2, @r output; PRINT @r;
Here is the output
Code language: SQL (Structured Query Language) (sql)
5
Because no exception occurred in the TRY block, the stored procedure completed at the TRY block.
Third, attempt to divide 20 by zero by calling the usp_divide stored procedure:
Code language: SQL (Structured Query Language) (sql)
DECLARE @r2 decimal; EXEC usp_divide 10, 0, @r2 output; PRINT @r2;
The following picture shows the output:
Because of division by zero error which was caused by the formula, the control was passed to the statement inside the CATCH block which returned the error’s detailed information.
SQL Serer TRY CATCH with transactions
Inside a CATCH block, you can test the state of transactions by using the XACT_STATE() function.
- If the
XACT_STATE()function returns -1, it means that an uncommittable transaction is pending, you should issue aROLLBACK TRANSACTIONstatement. - In case the
XACT_STATE()function returns 1, it means that a committable transaction is pending. You can issue aCOMMIT TRANSACTIONstatement in this case. - If the
XACT_STATE()function return 0, it means no transaction is pending, therefore, you don’t need to take any action.
It is a good practice to test your transaction state before issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement in a CATCH block to ensure consistency.
Using TRY CATCH with transactions example
First, set up two new tables sales.persons and sales.deals for demonstration:
Code language: SQL (Structured Query Language) (sql)
CREATE TABLE sales.persons ( person_id INT PRIMARY KEY IDENTITY, first_name NVARCHAR(100) NOT NULL, last_name NVARCHAR(100) NOT NULL ); CREATE TABLE sales.deals ( deal_id INT PRIMARY KEY IDENTITY, person_id INT NOT NULL, deal_note NVARCHAR(100), FOREIGN KEY(person_id) REFERENCES sales.persons( person_id) ); insert into sales.persons(first_name, last_name) values ('John','Doe'), ('Jane','Doe'); insert into sales.deals(person_id, deal_note) values (1,'Deal for John Doe');
Next, create a new stored procedure named usp_report_error that will be used in a CATCH block to report the detailed information of an error:
Code language: SQL (Structured Query Language) (sql)
CREATE PROC usp_report_error AS SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_LINE () AS ErrorLine ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_MESSAGE() AS ErrorMessage; GO
Then, develop a new stored procedure that deletes a row from the sales.persons table:
Code language: SQL (Structured Query Language) (sql)
CREATE PROC usp_delete_person( @person_id INT ) AS BEGIN BEGIN TRY BEGIN TRANSACTION; -- delete the person DELETE FROM sales.persons WHERE person_id = @person_id; -- if DELETE succeeds, commit the transaction COMMIT TRANSACTION; END TRY BEGIN CATCH -- report exception EXEC usp_report_error; -- Test if the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT N'The transaction is in an uncommittable state.' + 'Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test if the transaction is committable. IF (XACT_STATE()) = 1 BEGIN PRINT N'The transaction is committable.' + 'Committing transaction.' COMMIT TRANSACTION; END; END CATCH END; GO
In this stored procedure, we used the XACT_STATE() function to check the state of the transaction before performing COMMIT TRANSACTION or ROLLBACK TRANSACTION inside the CATCH block.
After that, call the usp_delete_person stored procedure to delete the person id 2:
Code language: SQL (Structured Query Language) (sql)
EXEC usp_delete_person 2;
There was no exception occurred.
Finally, call the stored procedure usp_delete_person to delete person id 1:
Code language: SQL (Structured Query Language) (sql)
EXEC usp_delete_person 1;
The following error occurred:
In this tutorial, you have learned how to use the SQL Server TRY CATCH construct to handle exceptions in stored procedures.
Новое полезное дополнение для 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, рекомендуется к прочтению:
- Детальный взгляд на RaiseError в SQL Server 2005
- Использование 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.