How to become an author
Search
Write a publication
Pull to refresh

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

Reading time11 min
Views56K
SQL*
Translation
Original author: Niladri Biswas
Новое полезное дополнение для 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.
Tags:
Hubs:
+21
Comments2
69
Karma
0
Rating
Андрей Гордиенков @VioletTape

Пользователь

Send message

Articles

Show the best of all time
Change theme settings

Your account

Sections

Information

Services

Support
© 2006–2024, Habr