Error procedure ms sql

The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. This article is completely compatible with MS SQL Server 2012 and above versions.

Problem Definition

There are many questions in
MSDN forum and other Internet communities about Error Handling in SQL Server. There are several issues as presented in the Table of Contents above.

Introduction

There are many articles written by the best experts and there are complete references about Error Handling in SQL Server. The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. Therefore I will try
to address this topic from a problem-solving approach and particularly in SQL Server 2012 (and later) versions. So the road map of this article is to cover the above questions as well as providing a step by step tutorial to design a structured mechanism for
error handling in SQL Server 2012 (and up) procedures. 

Solution

Is there any structured Error Handling mechanism in SQL Server?

Yes, there is. The TRY/CATCH construct is the structured mechanism for error handling in SQL Server 2005 and later. This construct has two parts; we can try executing some statements in
TRY block and handling errors in the CATCH block if they occur. Therefore, the simplest error handling structure can be like this:

• TRY
  • Try executing statements  
• CATCH
  • Handle the errors if they occur

Here is a sample code to provide the above structure in the simplest form:

—result

Will all statements in TRY block try to execute?

When executing statements in the TRY block, if an error occurs the flow of execution will transfer to the
CATCH block. So the answer is NO!

We can see this behavior with an example. As we can see after executing the following code, the statement no. 3 does not try executing, because the flow of execution will transfer to the CATCH block as soon as statement no. 2 raises
an error.

—result

Does the CATCH part automatically handle the errors?

No. The role of the TRY/CATCH construct is just providing a mechanism to try executing SQL statements. Therefore, we need to use another approach to handle the errors in the CATCH block that I’ll explain later. For instance, the following code will try to
execute a divide by zero statements. It does not automatically handle any errors. In fact, in this sample code, when an error occurs the flow control immediately transfers to the CATCH block, but in the CATCH block, we do not have any statement to tell us
that there was an error!

—result

In the CATCH block we can handle the error and send the error message to the application. So we need an element to show what error occurs. This element is
RAISERROR. So the error handling structure could be like this:

• TRY
  • Try executing statements 
• CATCH
  • Handle the error if occurs
    • RAISERROR

Here is sample code to produce the above structure:

—result

The RAISERROR itself needs other elements to identify the error number, error message, etc. Now we can complete the error handling structure:

• TRY
  • Try executing statements 
• CATCH
  • Handle the error if occurs
    • RAISERROR
      • ERROR_NUMBER()
      • ERROR_MESSAGE()
      • ERROR_SEVERITY()
      • ERROR_STATE()
      • ERROR_PROCEDURE()
      • ERROR_LINE()

Here is sample code to produce the above structure:

 —result

Is it a good idea to use a general procedure as a modular Error Handler routine?

From a modular programming approach, it’s recommended to create a stored procedure that does the RAISERROR job. But I believe that using a modular procedure (I call it spErrorHandler) to re-raise errors is not a good idea. Here are my reasons:

1. When we call RAISERROR in procedure “spErrorHandler”, we have to add the name of the procedure that the error occurs within to the Error Message. This will confuse the application end-users
(Customer). Customer does not want to know which part of his car is damaged. He prefers that his car just send him a simple message which tells him there is an error in its functions. In the software
world it’s more important to send a simple (English) message to the customer because if we send a complex error message, he will be afraid of what will happen to his critical data!

2. If we accept the first reason and decide to resolve this issue, we need to send a simple message to the client application. So we will lose the procedure name that the error occurs within and other useful information for debugging unless
we insert this useful information in an Error-Log table.

You can test this scenario with the following code:

 —result

As is illustrated in this figure, when using spErrorHandler, the values of ERROR_PROCEDURE() and ERROR_NUMBER() are changed in the output. This behavior is because of the RAISERROR functionality. This function always re-raises
the new exception, so spErrorHandler always shows that the value of ERROR_PROCEDURE() simply is “spErrorHandler”. As I said before there are two workarounds to fix this issue. First is concatenating this useful data with the error message and raise it, which
I spoke about in reason one. Second is inserting this useful data in another table just before we re-raise the error in spErrorHandler.

Now, we test the above sample without using spErrorHandler:

 —result

As you see in this figure, the procedure name and error number are correct. By the way, I prefer that if one customer reports an error, I go for SQL Server Profiler, simulate the environment completely, and test those SQL statements
in SSMS to recreate the error and debug it based on the correct error number and procedure name.

In the THROW section, I will explain that the main advantage of THROW over RAISERROR is that it shows the correct line number of the code that raises the error, which is so helpful for a developer in debugging his code.

3. Furthermore, with the THROW statement introduced in SQL SERVER 2012, there is no need to write extra code in the CATCH block. Therefore there is no need to write a separate procedure except for tracking the errors in another error log table.
In fact, this procedure is not an error handler, it’s an error tracker. I  will explain the THROW statement in the next section.

What are the benefits of THROW when we have RAISERROR?

The main objective of error handling is that the customer knows that an error occurred and reports it to the software developer. Then the developer can quickly realize the reason for the error and improve his code. In fact, error handling is a mechanism
that eliminates the blindness of both customer and developer.

To improve this mechanism Microsoft SQL Server 2012 introduced the THROW statement. Now I will address the benefits of THROW over RAISERROR.

The correct line number of the error!

As I said earlier this is the main advantage of using THROW. The following code will demonstrate this great feature:

 —result

As you can see in this figure, the line number of the error that RAISERROR reports to us always is the line number of itself in the code. But the error line number reported by THROW is line 6 in this example, which is the line
where the error occurred.

Easy to use

Another benefit of using the THROW statement is that there is no need for extra code in RAISERROR.

Complete termination

The severity level raised by THROW is always 16. But the more important feature is that when the THROW statement in a CATCH block is executed, then other code after this statement will never run.

The following sample script shows how this feature protects the code compared to RAISERROR:

 —result

Independence of sys.messages

This feature makes it possible to re-throw custom message numbers without the need to use
sp_addmessage to add the number.The feature is in real time, as you can see
in this code:

 —result

Tip

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

I want to check a condition in the TRY block. How can I control the flow of execution and raise the error?

This is a simple job! Now I change this question to this one:

“How can I terminate the execution of the TRY block?”

The answer is using THROW in the TRY block. Its severity level is 16, so it will terminate execution in the TRY block. We know that when any statement in the TRY block terminates (encounters an error) then immediately execution
goes to the CATCH block. In fact, the main idea is to THROW a custom error as in this code:

  —result

As you can see, we handle the error step by step. In the next session, we will complete this structure.

Does the CATCH part automatically rollback the statements within the TRY part?

This is the misconception that I sometimes hear. I will explain this problem with a little example. After executing the following code the table “dbo.Saeid” still exists. This demonstrates that the TRY/CATCH block does not implement
implicit transactions.

—result

Can someone use TRANSACTION in the TRY/CATCH block?

The previous question showed that if we want to rollback entire statements in a try block, we need to use explicit transactions in the  TRY block. But the main question here is:

“Where is the right place to commit and rollback? “

It’s a complex discussion that I would not like to jump into in this article. But there is a simple template that we can use for procedures (not triggers!).

This is that template:

—result

The elements of this structure are:

• TRY block
  • XACT_ABORT
  • Begin transaction
    • Statements to try
  • Commit transaction
• CATCH block
  • Check @@TRANCOUNT and rollback all transactions
  • THROW

Here is a short description of two parts of the above code:

XACT_ABORT

In general, it’s recommended to set the XACT_ABORT option to ON in our TRY/CATCH block in procedures. By setting this option to ON if we want to roll back the transaction, any user-defined transaction is rolled back.

@@TRANCOUNT

We check this global variable to ensure there is no open transaction. If there is an open transaction it’s time to execute rollback statements. This is a must in all CATCH blocks, even if you do not have any transactions in that
procedure. An alternative is to use XACT_STATE(). 

Conclusion

Introduction of the THROW statement is a big feat in Error Handling in SQL Server 2012. This statement enables database developers to focus on accurate line numbers of the procedure code. This article provided
a simple and easy to use error handling mechanism with minimum complexity using SQL Server 2012. By the way, there are some more complex situations that I did not cover in this article. If you need to dive deeper, you can see the articles in the
See Also section.

BOL link http://technet.microsoft.com/en-us/library/ms175976.aspx

See Also

Related Wiki Articles

Other Languages

• Mecanismo de Tratamento de erros em SQL Server 2012 (pt-BR)

Since the release of SQL Server 2005, you’ve been able to handle errors in your T-SQL code by including a TRY…CATCH block that controls the flow of your script should an error occur, similar to how procedural languages have traditionally handled errors. The TRY…CATCH block makes it easy to return or audit error-related data, as well as take other actions. And within the block-specifically, the CATCH portion-you’ve been able to include a RAISERROR statement in order to re-throw error-related data to the calling application. However, with the release of SQL Server 2012, you now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

In this article, we’ll look at the TRY…CATCH block used with both the RAISERROR and THROW statements. The examples are based on a table I created in the AdventureWorks2012 sample database, on a local instance of SQL Server 2012. Listing 1 shows the T-SQL script I used to create the LastYearSales table.

Listing 1: Creating the LastYearSales table

The script should be fairly straightforward. I use a SELECT…INTO statement to retrieve data from the Sales.vSalesPerson view and insert it into the newly created table. However, to demonstrate how to handle errors, we need to add one more element to our table: a check constraint that ensures the SalesLastYear value is never less than zero. Listing 2 shows the ALTERTABLE statement I used to add the constraint.

Listing 2: Adding a check constraint to the LastYearSales table

The constraint makes it easy to generate an error when updating the table. All I have to do is try to add a negative amount to the SalesLastYear column, an amount large enough to cause SQL Server to throw an error. Once we’ve created our table and added the check constraint, we have the environment we need for the examples in this article. You can just as easily come up with your own table and use in the examples. Just be sure you have a way of violating a constraint or you come up with another mechanism to generate an error. The goal is to create a script that handles any errors.

Working with the TRY…CATCH Block

Once we’ve set up our table, the next step is to create a stored procedure that demonstrates how to handle errors. The procedure, UpdateSales, modifies the value in the SalesLastYear column in the LastYearSales table for a specified salesperson. It works by adding or subtracting an amount from the current value in that column. Listing 3 shows the script I used to create the procedure. Notice that I include two input parameters-@SalesPersonID and @SalesAmt-which coincide with the table’s SalesPersonID and SalesLastYear columns.

Listing 3: Creating a stored procedure that contains a Try…Catch block

The main body of the procedure definition, enclosed in the BEGIN…END block, contains the TRY…CATCH block, which itself is divided into the TRY block and the CATCH block. The TRY block starts with BEGINTRY and ends with ENDTRY and encloses the T-SQL necessary to carry out the procedure’s actions. In this case, I include an UPDATE statement that adds the @SalesAmount value to the SalesLastYear column. The statement is enclosed in BEGINTRANSACTION and COMMITTRANSACTION statements to explicitly start and commit the transaction. Examples vary in terms of where they include the transaction-related statements. (Some don’t include the statements at all.) Just keep in mind that you want to commit or rollback your transactions at the appropriate times, depending on whether an error has been generated.

If the UPDATE statement runs successfully, the SalesLastYear value is updated and the operation is completed, in which case, the code in the CATCH block is never executed. However, if the UPDATE statement fails and SQL Server generates an error, the transaction is terminated and the database engine jumps to the CATCH block. The CATCH block starts with BEGINCATCH and ends with ENDCATCH and encloses the statements necessary to handle the error.

For the stored procedure in Listing 3, the first step I take in the CATCH block is to roll back the transaction if it is still running. I start by using the @@TRANCOUNT function to determine whether any transactions are still open. @@TRANCOUNT is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so I roll back that transaction.

Next, I declare a set of variables based on system functions that SQL Server makes available within the scope of the CATCH block. The functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose:

• ERROR_NUMBER(): The number assigned to the error.
• ERROR_LINE(): The line number inside the routine that caused the error.
• ERROR_MESSAGE(): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.
• ERROR_SEVERITY(): The error’s severity.
• ERROR_STATE(): The error’s state number.
• ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.

For this example, I use all but the last function, though in a production environment, you might want to use that one as well.

After I declare the variables, I include two PRINT statements that display the values of the @ErrorNumber and @ErrorLine variables (along with some explanatory text). The reason I do this is to demonstrate the difference between what the actual values are and what the RAISERROR statement returns, as you’ll see shortly.

The RAISERROR statement comes after the PRINT statements. The statement returns error information to the calling application. Generally, when using RAISERROR, you should include an error message, error severity level, and error state. The rules that govern the RAISERROR arguments and the values they return are a bit complex and beyond the scope of this article, but for the purposes of this example, I simply pass in the @ErrorMessage, @ErrorSeverity, and @ErrorState variables as arguments.

NOTE: For more information about the RAISERROR statement, see the topic “RAISERROR (Transact-SQL)” in SQL Server Books Online.

That’s basically all you need to do to create a stored procedure that contains a TRY…CATCH block. In a moment, we’ll try out our work. But first, let’s retrieve a row from the LastYearSales table to see what the current value is for salesperson 288. Listing 4 shows the SELECT statement I used to retrieve the data.

Listing 4: Retrieving date from the LastYearSales table

Not surprisingly, the statement returns the name and total sales for this salesperson, as shown in Listing 5. As you can see, Rachel Valdez shows over $1.3 million dollars in sales for last year.

Listing 5: Data retrieved from the LastYearSales table

Now let’s try out the UpdateSales stored procedure. Just for fun, let’s add a couple million dollars to Rachel Valdez’s totals. Listing 6 shows how I use the EXEC statement to call the procedure and pass in the salesperson ID and the $2 million.

Listing 6: Running the UpdateSales stored procedure

The stored procedure should run with no problem because we’re not violating the check constraint. If we were to execute the SELECT statement again (the one in Listing 4), our results would look similar to those shown in Listing 7. Notice all the extra cash.

Listing 7: Viewing the updated sales amount in the LastYearSales table

Now let’s look what happens if we subtract enough from her account to bring her totals to below zero. In listing 8, I run the procedure once again, but this time specify -4000000 for the amount.

Listing 8: Causing the UpdateSales stored procedure to throw an error

As you’ll recall, after I created the LastYearSales table, I added a check constraint to ensure that the amount could not fall below zero. As a result, the stored procedure now generates an error, which is shown in Listing 9.

Listing 9: The error message returned by the UpdateSales stored procedure

As expected, the information we included in the CATCH block has been returned. But notice that the actual error number (547) is different from the RAISERROR message number (50000) and that the actual line number (9) is different from the RAISERROR line number (27). In theory, these values should coincide. But as I mentioned earlier, the rules that govern RAISERROR are a bit quirky.

Working with the THROW Statement

To simplify returning errors in a CATCH block, SQL Server 2012 introduced the THROW statement. With the THROW statement, you don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

NOTE: You can use the THROW statement outside of the CATCH block, but you must include parameter values to do so. For more information about the THROW statement, see the topic “THROW (Transact-SQL)” in SQL Server Books Online.

To demonstrate the THROW statement, I defined an ALTER PROCEDURE statement that modifies the UpdateSales procedure, specifically the CATCH block, as shown in Listing 10.

Listing 10: Altering the UpdateSales stored procedure

Notice that I retain the @ErrorNumber and @ErrorLine variable declarations and their related PRINT statements. I do so only to demonstrate the THROW statement’s accuracy. In actually, I need only to roll back the transaction and specify the THROW statement, without any parameters.

Now let’s execute the stored procedure again, once more trying to deduct $4 million from the sales amount, as shown in Listing 11.

Listing 11: Causing the UpdateSales stored procedure to throw an error

Once again, SQL Server returns an error. Only this time, the information is more accurate. As you can see in Listing 12, the message numbers and line numbers now match. No longer do we need to declare variables or call system functions to return error-related information to the calling application.

Listing 12: The error message returned by the UpdateSales stored procedure

As you can see, SQL Server 2012 makes handling errors easier than ever. Even if you’ve been using the TRY…CATCH block for a while, the THROW statement should prove a big benefit over RAISERROR. And if you’re new to error handling in SQL Server, you’ll find that the TRY…CATCH block and the THROW statement together make the process a fairly painless one, one well worth the time and effort it takes to learn and implement them.

Новое полезное дополнение для 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.