Which sql statement will return an error

Error and Transaction Handling in SQL Server

Part One – Jumpstart Error Handling

An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2022-03-18.
Copyright applies to this text. See here for font conventions used in this article.

This part is also available in a Spanish translation by Geovanny Hernandez and in a Russian translation by Alexey Guzev.

Introduction

This article is the first in a series of three about error and transaction handling in SQL Server. The aim of this first article is to give you a jumpstart with error handling by showing you a basic pattern which is good for the main bulk of your code. This part is written with the innocent and inexperienced reader in mind, and for this reason I am intentionally silent on many details. The purpose here is to tell you how without dwelling much on why . If you take my words for your truth, you may prefer to only read this part and save the other two for a later point in your career.

On the other hand, if you question my guidelines, you certainly need to read the other two parts, where I go into much deeper detail exploring the very confusing world of error and transaction handling in SQL Server. Parts Two and Three, as well as the three appendixes, are directed towards readers with a more general programming experience, although necessarily not with SQL Server. This first article is short; Parts Two and Three are considerably longer.

Table of Contents

Index of All Error-Handling Articles

Here follows a list of all articles in this series:

Part One – Jumpstart Error Handling (this article).

Appendix 2 – CLR. (Extends both Parts Two and Three.)

All the articles above are for SQL 2005 and later. For those who still are on SQL 2000, there are two older articles:

Why Error Handling?

Why do we have error handling in our code? There are many reasons. In a forms application we validate the user input and inform the users of their mistakes. These user mistakes are anticipated errors. But we also need to handle unanticipated errors. That is, errors that occur because we overlooked something when we wrote our code. A simple strategy is to abort execution or at least revert to a point where we know that we have full control. It cannot be enough stressed that it is entirely impermissible to ignore an unanticipated error. This is a sin that can have grave consequences: it could cause the application to present incorrect information to the user or even worse to persist incorrect data in the database. It is also important to communicate that an error has occurred, lest that the user thinks that the operation went fine, when your code in fact performed nothing at all.

In a database system, we often want updates to be atomic. For instance, say that the task is to transfer money from one account to another. To this end, we need to update two rows in the CashHoldings table and add two rows to the Transactions table. It’s absolutely impermissible that an error or an interruption would result in money being deposited into the receiving account without it being withdrawn from the other. For this reason, in a database application, error handling is also about transaction handling. In this example, we need to wrap the operation in BEGIN TRANSACTION and COMMIT TRANSACTION , but not only that: in case of an error, we must make sure that the transaction is rolled back.

Essential Commands

We will start by looking at the most important commands that are needed for error handling. In Part Two, I cover all commands related to error and transaction handling.

TRY-CATCH

The main vehicle for error handling is TRY-CATCH , very reminiscent of similar constructs in other languages. The structure is:

If any error occurs in , execution is transferred to the CATCH block, and the error-handling code is executed. Typically, your CATCH rolls back any open transaction and reraises the error, so that the calling client program understand that something went wrong. As for how to reraise the error, we will come to this later in this article.

Here is a very quick example:

This is the error: Divide by zero error encountered.

We will return to the function error_message() later. It is worth noting that using PRINT in your CATCH handler is something you only would do when experimenting. You should never do so in real application code.

If calls stored procedures or invokes triggers, any error that occurs in these will also transfer execution to the CATCH block. More exactly, when an error occurs, SQL Server unwinds the stack until it finds a CATCH handler, and if there isn’t any, SQL Server sends the error message to the client.

There is one very important limitation with TRY-CATCH you need to be aware of: it does not catch compilation errors that occur in the same scope. Consider:

Msg 208, Level 16, State 1, Procedure inner_sp, Line 4

Invalid object name ‘NoSuchTable’.

As you see the TRY block is entered, but when the error occurs, execution is not transferred to the CATCH block as expected. This is true for all compilation errors such as missing columns, incorrect aliases etc that occur at run-time. (Compilation errors can occur at run-time in SQL Server due to deferred name resolution, a (mis)feature where SQL Server permits you to create a procedure that refers to non-existing tables.)

These errors are not entirely uncatchable; you cannot catch them in the scope they occur, but you can catch them in outer scopes. Add this code to the example above:

Now we get this output:

The error message is: Invalid object name ‘NoSuchTable’.

This time the error is caught because there is an outer CATCH handler.

SET XACT_ABORT ON

Your stored procedures should always include this statement in the beginning:

This turns on two session options that are off by default for legacy reasons, but experience has proven that best practice is to always have them on. The default behaviour in SQL Server when there is no surrounding TRY-CATCH is that some errors abort execution and roll back any open transaction, whereas with other errors execution continues on the next statement. When you activate XACT_ABORT ON , almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement.

The option XACT_ABORT is essential for a more reliable error and transaction handling. Particularly, with the default behaviour there are several situations where execution can be aborted without any open transaction being rolled back, even if you have TRY-CATCH . We saw one such example in the previous section where we learnt that TRY-CATCH does not catch compilations errors in the same scope. An open transaction which is not rolled back in case of an error can cause major problems if the application jogs along without committing or rolling back.

For good error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON . Of these two, SET XACT_ABORT ON is the most important. For production-grade code it’s not really sufficient to rely on XACT_ABORT , but for quick and simple stuff it can do.

The option NOCOUNT has nothing to do with error handling, but I included in order to show best practice. The effect of NOCOUNT is that it suppresses messages like (1 row(s) affected) that you can see in the Message tab in SQL Server Management Studio. While these row counts can be useful when you work interactively in SSMS, they can degrade performance in an application because of the increased network traffic. The row counts can also confuse poorly written clients that think they are real result sets.

Above, I’ve used a syntax that is a little uncommon. Most people would probably write two separate statements:

There is no difference between this and the above. I prefer the version with one SET and a comma since it reduces the amount of noise in the code. As these statements should appear in all your stored procedures, they should take up as little space as possible.

General Pattern for Error Handling

Having looked at TRY-CATCH and SET XACT_ABORT ON , let’s piece it together to a pattern that we can use in all our stored procedures. To take it slow and gentle, I will first show an example where I reraise the error in a simple-minded way, and in the next section I will look into better solutions.

For the example, I will use this simple table.

Here is a stored procedure that showcases how you should work with errors and transactions.

The first line in the procedure turns on XACT_ABORT and NOCOUNT in single statement as I showed above. This line is the only line to come before BEGIN TRY . Everything else in the procedure should come after BEGIN TRY : variable declarations, creation of temp tables, table variables, everything. Even if you have other SET commands in the procedure (there is rarely a reason for this, though), they should come after BEGIN TRY .

The reason I prefer to have SET XACT_ABORT , NOCOUNT ON before BEGIN TRY is that I see this as one line of noise: it should always be there, but that I don’t want it to strain my eyes. This is certainly a matter of preference, and if you prefer to put the SET commands after BEGIN TRY , that’s alright. What is important is that you should never put anything else before BEGIN TRY .

The part between BEGIN TRY and END TRY is the main meat of the procedure. Because I wanted to include a user-defined transaction, I introduced a fairly contrived business rule which says that when you insert a pair, the reverse pair should also be inserted. The two INSERT statements are inside BEGIN and COMMIT TRANSACTION . In many cases you will have some lines code between BEGIN TRY and BEGIN TRANSACTION . Sometimes you will also have code between COMMIT TRANSACTION and END TRY , although that is typically only a final SELECT to return data or assign values to output parameters. If your procedure does not perform any updates or only has a single INSERT / UPDATE / DELETE / MERGE statement, you typically don’t have an explicit transaction at all.

Whereas the TRY block will look different from procedure to procedure, the same is not true for the CATCH block. Your CATCH blocks should more or less be a matter of copy and paste. That is, you settle on something short and simple and then use it all over the place without giving it much thinking. The CATCH handler above performs three actions:

1. Rolls back any open transaction.
2. Reraises the error.
3. Makes sure that the return value from the stored procedure is non-zero.

These actions should always be there. Always. You may argue that the line

is not needed if there no explicit transaction in the procedure, but nothing could be more wrong. Maybe you call a stored procedure which starts a transaction, but which is not able to roll it back because of the limitations of TRY-CATCH . Maybe you or someone else adds an explicit transaction to the procedure two years from now. Will you remember to add the line to roll back then? Don’t count on it. I can also hear readers that object if the caller started the transaction we should not roll back. . Yes, we should, and if you want to know why you need to read Parts Two and Three. Always rolling back the transaction in the CATCH handler is a categorical imperative that knows of no exceptions.

The code for reraising the error includes this line:

The built-in function error_message() returns the text for the error that was raised. On the next line, the error is reraised with the RAISERROR statement. This is an unsophisticated way to do it, but it does the job. We will look at alternatives in the next chapter.

Note : the syntax to give variables an initial value with DECLARE was introduced in SQL 2008. If you are on SQL 2005, you will need to split the line in one DECLARE and one SELECT statement.

Always reraise? What if you only want to update a row in a table with the error message? Yes, that is a situation that occurs occasionally, although you would typically do that in an inner CATCH block which is part of a loop. (I have a longer example demonstrating this in Part Three.) The outer CATCH block in a procedure is exactly for catching and reraising unexpected errors you did not foresee. Dropping these errors on the floor is a criminal sin. They must be reraised.

The final RETURN statement is a safeguard. Recall that RAISERROR never aborts execution, so execution will continue with the next statement. As long as all procedures are using TRY-CATCH and likewise all client code is using exception handling this is no cause for concern. But your procedure may be called from legacy code that was written before SQL 2005 and the introduction of TRY-CATCH . In those days, the best we could do was to look at return values. What you return does not really matter, as long as it’s a non-zero value. (Zero is usually understood as success.)

The last statement in the procedure is END CATCH . You should never have any code after END CATCH for the outermost TRY-CATCH of your procedure. For one thing, anyone who is reading the procedure will never see that piece of code.

Having read all the theory, let’s try a test case:

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

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

Let’s add an outer procedure to see what happens when an error is reraised repeatedly:

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

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

We get the correct error message, but if you look closer at the headers of this message and the previous, you may note a problem:

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

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

The error messages give the location of the final RAISERROR statement that was executed. In the first case, only the line number is wrong. In the second case, the procedure name is incorrect as well. For simple procedures like our test procedures, this is not a much of an issue, but if you have several layers of nested complex stored procedures, only having an error message but not knowing where it occurred makes your troubleshooting a lot more difficult. For this reason, it is desirable to reraise the error in such a way that you can locate the failing piece of code quickly, and this is what we will look at in the next chapter.

Three Ways to Reraise the Error

Using error_handler_sp

We have seen error_message() , which returns the text for an error message. An error message consists of several components, and there is one error_xxx() function for each one of them. We can use this to reraise a complete message that retains all the original information, albeit with a different format. Doing this in each and every CATCH handler would be a gross sin of code duplication, and there is no reason to. You don’t have to be in the CATCH block to call error_message() & co, but they will return exactly the same information if they are invoked from a stored procedures that your CATCH block calls.

Let me introduce to you error_handler_sp :

The first thing error_handler_sp does is to capture the value of all the error_xxx() functions into local variables. (Exactly what all these mean, is something I am not covering in this introductory article, but I leave that for Part Two.) I will return to the IF statement in a second. Instead let’s first look at the SELECT statement inside of it:

The purpose of this SELECT statement is to format an error message that we pass to RAISERROR , and which includes all information in the original error message which we cannot inject directly into RAISERROR . We need to give special treatment to the procedure name, since it will be NULL for errors that occur in ad-hoc batches or in dynamic SQL. Whence the use of the coalesce() function. (If you don’t really understand the form of the RAISERROR statement, I discuss this in more detail in Part Two.)

The formatted error message starts with three asterisks. This serves two purposes: 1) We can directly see that this is a message reraised from a CATCH handler. 2) This makes it possible for error_handler_sp to filter out errors it has reraised once or more already with the condition NOT LIKE ‘***%’ to avoid that error messages get modified a second time.

Here is how a CATCH handler should look like when you use error_handler_sp :

Let’s try some test cases.

This results in:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20

*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column ‘b’, table ‘tempdb.dbo.sometable’; column does not allow nulls. INSERT fails.

Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20

*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

The header of the messages say that the error occurred in error_handler_sp , but the texts of the error messages give the original location, both procedure name and line number.

I will present two more methods to reraise errors. However, error_handler_sp is my main recommendation for readers who only read this part. It’s simple and it works on all versions of SQL Server from SQL 2005 and up. There is really only one drawback: in some situations SQL Server raises two error messages, but the error_xxx() functions return only information about one of them, and thus one of the error messages is lost. This can be quite difficult with administrative commands like BACKUP / RESTORE , but it is rarely an issue in pure application code.

Using ;THROW

In SQL 2012, Microsoft introduced the ; THROW statement to make it easier to reraise errors. Unfortunately, Microsoft made a serious design error with this command and introduced a dangerous pitfall.

With ; THROW you don’t need any stored procedure to help you. Your CATCH handler becomes as simple as this:

The nice thing with ; THROW is that it reraises the error message exactly as the original message. If there were two error messages originally, both are reraised which makes it even better. As with all other errors, the errors reraised by ; THROW can be caught in an outer CATCH handler and reraised. If there is no outer CATCH handler, execution is aborted, so you do not need any RETURN statement.

If you have SQL 2012 or later, change the definition of insert_data and outer_sp , and try the tests cases again. The output this time:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5

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

Msg 2627, Level 14, State 1, Procedure insert_data, Line 6

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

The procedure name and line number are accurate and there is no other procedure name to confuse us. Also, the original error numbers are retained.

At this point you might be saying to yourself: he must be pulling my legs, did Microsoft really call the command ;THROW? Isn’t it just THROW? True, if you look it up in Books Online, there is no leading semicolon. But the semicolon must be there. Officially, it is a terminator for the previous statement, but it is optional, and far from everyone uses semicolons to terminate their T‑SQL statements. More importantly, if you leave out the semicolon before THROW this does not result in a syntax error, but in a run-time behaviour which is mysterious for the uninitiated. If there is an active transaction you will get an error message – but a completely different one from the original. Even worse, if there is no active transaction, the error will silently be dropped on the floor. Something like mistakenly leaving out a semicolon should not have such absurd consequences. To reduce the risk for this accident, always think of the command as ; THROW .

It should not be denied that ; THROW has its points, but the semicolon is not the only pitfall with this command. If you want to use it, I encourage you to read at least Part Two in this series, where I cover more details on ; THROW . Until then, stick to error_handler_sp .

Using SqlEventLog

The third way to reraise an error is to use SqlEventLog, which is a facility that I present in great detail in Part Three. Here I will only give you a teaser.

SqlEventLog offers a stored procedure slog.catchhandler_sp that works similar to error_handler_sp : it uses the error_xxx() functions to collect the information and reraises the error message retaining all information about it. In addition, it logs the error to the table slog.sqleventlog . Depending on the type of application you have, such a table can be a great asset.

To use SqlEventLog, your CATCH hander would look like this:

@@procid returns the object id of the current stored procedure, something that SqlEventLog uses when it writes the log information to the table. Using the same test cases, this is the output with catchhandler_sp :

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125

<515>Procedure insert_data, Line 5

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

Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125

<2627>Procedure insert_data, Line 6

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

As you see, the error messages from SqlEventLog are formatted somewhat differently from error_handler_sp , but the basic idea is the same. Here is a sample of what is logged to the table slog.sqleventlog :

logid logdate errno severity logproc linenum msgtext

1 2015-01-25 22:40:24.393 515 16 insert_data 5 Cannot insert .

2 2015-01-25 22:40:24.395 2627 14 insert_data 6 Violation of .

If you want to play with SqlEventLog right on the spot, you can download the file sqleventlog.zip. For installation instructions, see the section Installing SqlEventLog in Part Three.

You have now learnt a general pattern for error and transaction handling in stored procedures. It is not perfect, but it should work well for 90-95 % of your code. There are a couple of limitations you should be aware of:

1. As we have seen, compilation errors such as missing tables or missing columns cannot be trapped in the procedure where they occur, only in outer procedures.
2. The pattern does not work for user-defined functions, since neither TRY-CATCH nor RAISERROR are permitted there.
3. When you call a stored procedure on a linked server that raises an error, this error may bypass the error handler in the procedure on the local server and go to directly to the client.
4. When a procedure is called by INSERT-EXEC , you will get an ugly error, because ROLLBACK TRANSACTION is not permitted in this case.
5. As noted above, if you use error_handler_sp or SqlEventLog, you will lose one error message when SQL Server raises two error messages for the same error. This is not an issue with ; THROW .

I cover these situations in more detail in the other articles in the series.

Before I close this off, I like to briefly cover triggers and client code.

Triggers

The pattern for error handling in triggers is not any different from error handling in stored procedures, except in one small detail: you should not include that RETURN statement. (Because RETURN with a value is not permitted in triggers.)

What is important to understand about triggers is that they are part of the command that fired the trigger, and in a trigger you are always in a transaction, even if you did not use BEGIN TRANSACTION . Sometimes I see people in SQL Server forums ask if they can write a trigger that does not roll back the command that fired the trigger if the trigger fails. The answer is that there is no way that you can do this reliably, so you better not even try. If you have this type of requirement, you should probably not use a trigger at all, but use some other solution. In Parts Two and Three, I discuss error handling in triggers in more detail.

Client Code

Yes, you should have error handling in client code that accesses the database. That is, you should always assume that any call you make to the database can go wrong. Exactly how to implement error handling depends on your environment, and to cover all possible environments out there, I would have to write a couple of more articles. And learn all those environments.

Here, I will only point out one important thing: your reaction to an error raised from SQL Server should always be to submit this batch to avoid orphaned transactions:

This also applies to the famous message Timeout expired (which is not a message from SQL Server, but the client API).

I cover error handling in ADO .NET in the last chapter of Part 3. If you use old ADO, I cover this in my old article on error handling in SQL 2000.

End of Part One

This is the end of Part One of this series of articles. If you just wanted to learn the pattern quickly, you have completed your reading at this point. If your intention is to read it all, you should continue with Part Two which is where your journey into the confusing jungle of error and transaction handling in SQL Server will begin for real.

If you have questions, comments or suggestions specific to this article, please feel free to contact me at esquel@sommarskog.se. This includes small things like spelling errors, bad grammar, errors in code samples etc. Since I don’t have a publisher, I need to trust my readership to be my tech editors and proof-readers. 🙂 If you have questions relating to a problem you are working with, I recommend that you ask that question in a public forum, as this is more likely to give you a quick response.

For a list of acknowledgements, please see the end of Part Three. Below is a revision history for Part One.

. and don’t forget to add this line first in your stored procedures:

Источник

Время прочтения
16 мин

Просмотры 35K

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

1. Введение

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

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

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

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

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

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

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

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

2.1 TRY-CATCH

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2.2 SET XACT_ABORT ON

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

SET XACT_ABORT, NOCOUNT ON

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

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

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

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

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

SET NOCOUNT ON
SET XACT_ABORT ON

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

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

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

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

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

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

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

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

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

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

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

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

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

IF @@trancount > 0 ROLLBACK TRANSACTION

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

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

DECLARE @msg nvarchar(2048) = error_message()

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

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

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

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

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

EXEC insert_data 9, NULL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

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

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

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

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

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

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

Триггеры

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

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

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

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

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

IF @@trancount > 0 ROLLBACK TRANSACTION

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

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

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

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

SET XACT_ABORT, NOCOUNT ON

Error handling overview

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

In this article, we’ll take a closer look at the TRY… CATCH statement: the syntax, how it looks, how it works and what can be done when an error occurs. Furthermore, the method will be explained in a SQL Server case using a group of T-SQL statements/blocks, which is basically SQL Server way of handling errors. This is a very simple yet structured way of doing it and once you get the hang of it, it can be quite helpful in many cases.

On top of that, there is a RAISERROR function that can be used to generate our own custom error messages which is a great way to translate confusing error messages into something a little bit more meaningful that people would understand.

Handling errors using TRY…CATCH

Here’s how the syntax looks like. It’s pretty simple to get the hang of. We have two blocks of code:

Anything between the BEGIN TRY and END TRY is the code that we want to monitor for an error. So, if an error would have happened inside this TRY statement, the control would have immediately get transferred to the CATCH statement and then it would have started executing code line by line.

Now, inside the CATCH statement, we can try to fix the error, report the error or even log the error, so we know when it happened, who did it by logging the username, all the useful stuff. We even have access to some special data only available inside the CATCH statement:

• ERROR_NUMBER – Returns the internal number of the error
• ERROR_STATE – Returns the information about the source
• ERROR_SEVERITY – Returns the information about anything from informational errors to errors user of DBA can fix, etc.
• ERROR_LINE – Returns the line number at which an error happened on
• ERROR_PROCEDURE – Returns the name of the stored procedure or function
• ERROR_MESSAGE – Returns the most essential information and that is the message text of the error

That’s all that is needed when it comes to SQL Server error handling. Everything can be done with a simple TRY and CATCH statement and the only part when it can be tricky is when we’re dealing with transactions. Why? Because if there’s a BEGIN TRANSACTION, it always must end with a COMMIT or ROLLBACK transaction. The problem is if an error occurs after we begin but before we commit or rollback. In this particular case, there is a special function that can be used in the CATCH statement that allows checking whether a transaction is in a committable state or not, which then allows us to make a decision to rollback or to commit it.

Let’s head over to SQL Server Management Studio (SSMS) and start with basics of how to handle SQL Server errors. The AdventureWorks 2014 sample database is used throughout the article. The script below is as simple as it gets:

This is an example of how it looks and how it works. The only thing we’re doing in the BEGIN TRY is dividing 1 by 0, which, of course, will cause an error. So, as soon as that block of code is hit, it’s going to transfer control into the CATCH block and then it’s going to select all of the properties using the built-in functions that we mentioned earlier. If we execute the script from above, this is what we get:

We got two result grids because of two SELECT statements: the first one is 1 divided by 0, which causes the error and the second one is the transferred control that actually gave us some results. From left to right, we got ErrorNumber, ErrorState, ErrorSeverity; there is no procedure in this case (NULL), ErrorLine, and ErrorMessage.

Now, let’s do something a little more meaningful. It’s a clever idea to track these errors. Things that are error-prone should be captured anyway and, at the very least, logged. You can also put triggers on these logged tables and even set up an email account and get a bit creative in the way of notifying people when an error occurs.

If you’re unfamiliar with database email, check out this article for more information on the emailing system: How to configure database mail in SQL Server

The script below creates a table called DB_Errors, which can be used to store tracking data:

Here we have a simple identity column, followed by username, so we know who generated the error and the rest is simply the exact information from the built-in functions we listed earlier.

Now, let’s modify a custom stored procedure from the database and put an error handler in there:

Altering this stored procedure simply wraps error handling in this case around the only statement inside the stored procedure. If we call this stored procedure and pass some valid data, here’s what happens:

A quick Select statement indicates that the record has been successfully inserted:

However, if we call the above-stored procedure one more time, passing the same parameters, the results grid will be populated differently:

This time, we got two indicators in the results grid:

0 rows affected – this line indicated that nothing actually went into the Sales table

1 row affected – this line indicates that something went into our newly created logging table

So, what we can do here is look at the errors table and see what happened. A simple Select statement will do the job:

Here we have all the information we set previously to be logged, only this time we also got the procedure field filled out and of course the SQL Server “friendly” technical message that we have a violation:

Violation of PRIMARY KEY constraint ‘PK_Sales_1′. Cannot insert duplicate key in object’ Sales.Sales’. The duplicate key value is (20).

How this was a very artificial example, but the point is that in the real world, passing an invalid date is very common. For example, passing an employee ID that doesn’t exist in a case when we have a foreign key set up between the Sales table and the Employee table, meaning the Employee must exist in order to create a new record in the Sales table. This use case will cause a foreign key constraint violation.

The general idea behind this is not to get the error fizzled out. We at least want to report to an individual that something went wrong and then also log it under the hood. In the real world, if there was an application relying on a stored procedure, developers would probably have SQL Server error handling coded somewhere as well because they would have known when an error occurred. This is also where it would be a clever idea to raise an error back to the user/application. This can be done by adding the RAISERROR function so we can throw our own version of the error.

For example, if we know that entering an employee ID that doesn’t exist is more likely to occur, then we can do a lookup. This lookup can check if the employee ID exists and if it doesn’t, then throw the exact error that occurred. Or in the worst-case scenario, if we had an unexpected error that we had no idea what it was, then we can just pass back what it was.

Advanced SQL error handling

We only briefly mentioned tricky part with transactions, so here’s a simple example of how to deal with them. We can use the same procedure as before, only this time let’s wrap a transaction around the Insert statement:

So, if everything executes successfully inside the Begin transaction, it will insert a record into Sales, and then it will commit it. But if something goes wrong before the commit takes place and it transfers control down to our Catch – the question is: How do we know if we commit or rollback the whole thing?

If the error isn’t serious, and it is in the committable state, we can still commit the transaction. But if something went wrong and is in an uncommittable state, then we can roll back the transaction. This can be done by simply running and analyzing the XACT_STATE function that reports transaction state.

This function returns one of the following three values:

  1 – the transaction is committable

-1 – the transaction is uncommittable and should be rolled back

  0 – there are no pending transactions

The only catch here is to remember to actually do this inside the catch statement because you don’t want to start transactions and then not commit or roll them back:

How, if we execute the same stored procedure providing e.g. invalid EmployeeID we’ll get the same errors as before generated inside out table:

The way we can tell that this wasn’t inserted is by executing a simple Select query, selecting everything from the Sales table where EmployeeID is 20:

Generating custom raise error SQL message

Let’s wrap things up by looking at how we can create our own custom error messages. These are good when we know that there’s a possible situation that might occur. As we mentioned earlier, it’s possible that someone will pass an invalid employee ID. In this particular case, we can do a check before then and sure enough, when this happens, we can raise our own custom message like saying employee ID does not exist. This can be easily done by altering our stored procedure one more time and adding the lookup in our TRY block:

If this count comes back as zero, that means the employee with that ID doesn’t exist. Then we can call the RAISERROR where we define a user-defined message, and furthermore our custom severity and state. So, that would be a lot easier for someone using this stored procedure to understand what the problem is rather than seeing the very technical error message that SQL throws, in this case, about the foreign key validation.

With the last changes in our store procedure, there also another RAISERROR in the Catch block. If another error occurred, rather than having it slip under, we can again call the RAISERROR and pass back exactly what happened. That’s why we have declared all the variables and the results of all the functions. This way, it will not only get logged but also report back to the application or user.

And now if we execute the same code from before, it will both get logged and it will also indicate that the employee ID does not exist:

Another thing worth mentioning is that we can actually predefine this error message code, severity, and state. There is a stored procedure called sp_addmessage that is used to add our own error messages. This is useful when we need to call the message on multiple places; we can just use RAISERROR and pass the message number rather than retyping the stuff all over again. By executing the selected code from below, we then added this error into SQL Server:

This means that now rather than doing it the way we did previously, we can just call the RAISERROR and pass in the error number and here’s what it looks like:

The sp_dropmessage is, of course, used to drop a specified user-defined error message. We can also view all the messages in SQL Server by executing the query from below:

There’s a lot of them and you can see our custom raise error SQL message at the very top.

I hope this article has been informative for you and I thank you for reading.

References

• TRY…CATCH (Transact-SQL)
• RAISERROR (Transact-SQL)
• System Functions (Transact-SQL)

All Weeks SQL for Data Science Coursera Quiz Answers

SQL for Data Science Coursera Week 1 Quiz Answers

Quiz 1:Let’s Practice!

Q 1. This statement will return an error. Please list why.

SELECT
TrackID
Name
AlbumID
FROM tracks

• It doesn’t state where to get the data from
• It’s missing comma after “TrackID”, and “Name”
• It lists too many columns

Q 2. In the ER diagram below, the infinity symbol is representing a “many” relationship and the key is representing “one”. Select all the tables that have a one-to-many relationship.

Answer:

• Artist to Albums
• Customers to Invoices
• Employees to Customers

Q 3. When using SQLite, what datatypes can you assign to a column when creating a new table? Select all that apply.

Answer:

• Real
• Null
• Text
• Integer

Q 4. Primary Keys must be unique values.

True

False

Q 5. What is the query below missing in order to execute?

• Select
• From
• A Comma
• The Column Names

Quiz 2: Practice Simple Select Queries

Q 1. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run query: Retrieve all the data from the tracks table. Who is the composer for track 18?

Select *
From Tracks;

Answer:

left join Albums

on Tracks.AlbumId=Albums.AlbumId

where Albums.Title=”Californication”;

Q 2. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Retrieve all data from the artists table. Look at the list of artists, how many artists are you familiar with (there is no wrong answer here)?

Select *
From Artists;

Answer:

comment the Answer

Q 3. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Retrieve all data from the invoices table. What is the billing address for customer 31?

Select *
From Invoices;

Answer:

comment the Answer

Q 4. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the playlist id, and name from the playlists table. How many playlists are there? Which would you classify is your favorite from this list?

Select Playlistid,
Name
From Playlists;

answer:

Comment the Answer.

Q 5. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the Customer Id, Invoice Date, and Billing City from the Invoices table. What city is associated with Customer ID number 42? What was the invoice date for the customer in Santiago?

Select CustomerId,
InvoiceDate, 
BillingCity 
From Invoices;

Answer:

Comment the Answer.

Q 6. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the First Name, Last Name, Email, and Phone, from the Customers table. What is the telephone number for Jennifer Peterson?

Select FirstName, 
LastName, 
Email, 
Phone
From Customers;

Answer:

Comment the Answer.

Q 6. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the Track Id, Genre Id, Composer, Unit Price from the Tracks table. How much do these tracks cost?

Select TrackId, 
GenreId, 
Composer, 
UnitPrice 
From Tracks;

Answer:

Comment the Answer.

Q 8. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Playlist Track table and limit the results to 10 records. How might this information be used?

Select *
From Playlist_track 
Limit 10;

Answer:

comment the Answer.

Q 9. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Media Types table and limit the results to 50 records. What happened when you ran this query? Were you able to get all 50 records?

Select *
From Media_types
Limit 50;

Answer:

comment the Answer.

Q 10. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Albums table and limit the results to 5 records. How many columns are in the albums table?

What is the name of the 9th album in this list?

Select *
From Albums;

Answer:

Comment the Answer.

Quiz 3: Module 1 Quiz

Q 1. Select the jobs below that may use SQL in their work (select all that apply).

Answer.

• DBA
• Backend Developer
• Data Scientist
• QA Engineer
• Data Analyst

Q 2. How does a data scientist and DBA differ in how they use SQL?

• Data scientists only query the database and don’t create tables.
• Data scientists don’t write complex queries.
• DBA’s are the only ones who merge datasets together.
• DBAs manage the database for other users.

Q 3. Which of the following statements are true of Entity Relationship (ER) Diagrams?

Answer:

• They are usually a representation of a business process.
• They speed up your querying time.
• They identify the Primary Keys
• They usually are represented in a visual format.
• They show you the relationships between tables.
• They only represent entities in the diagram.

Q 4. Select the query below that will retrieve all columns from the customers table.

Answer:

SELECT * FROM customers

Q 5.Select the query that will retrieve only the Customer First Name, Last Name, and Company.

Answer:

SELECT 
FirstName
,LastName
,Company
FROM customers

Q 6. The ER diagram below is depicting what kind of relationship between the EMPLOYEES and CUSTOMERS tables?

• One-to-one
• One-to-many
• Many-to-one
• Many-to-many

Q 7. The data model depicted in the ER diagram below could be described as a _______________.

• Transactional Model
• Star Schema
• Relational Model

Q 8. When using the “CREATE TABLE” command and creating new columns for that table, which of the following statements is true?

• You must assign a data type to each column
• You must insert data into all the columns while creating the table
• You can create the table and then assign data types later

Q 9. Look at the values in the two columns below. Based on the values in each column, which column could potentially be used as a primary key?

Column 1	Column 2
5	2
6	4
1	5
2	5
34	32
8	6
9	4

• Column 1
• Column 2
• Column 1 OR Column 2

Q 10. In order to retrieve data from a table with SQL, every SQL statement must contain?

• CREATE
• SELECT
• WHERE
• FIND

Quiz 4: Module 1 Coding Questions

Q 1. For all of the questions in this quiz, we are using the Chinook database. All of the interactive code blocks have been setup to retrieve data only from this database.

Retrieve all the records from the Employees table.

What is Robert King’s mailing address? Note: You will have to scroll to the right in order to see it.

• 1111 6 Ave SW, Calgary, AB, CANADA T2P 5M5
• 590 Columbia Boulevard West, Lethbridge, AB, CANADA T1K 5N8
• 683 10 Street SW, Calgary, AB, CANADA T2P 5G3
• 11120 Jasper Ave. NW, Edmonton, AB, CANADA T5K 2N1

Q 2. Retrieve the FirstName, LastName, Birthdate, Address, City, and State from the Employees table.

Which of the employees listed below has a birthdate of 3-3-1965?

• Michael
• Robert
• Steve
• Jane
• Nancy

Q 3. Retrieve all the columns from the Tracks table, but only return 20 rows.

What is the runtime in milliseconds for the 5th track, entitled “Princess of the Dawn”? Note: You will need to scroll to the right to see it, and you may want to copy and paste the number to ensure it is entered correctly.

Answer:

comment the Answer.

SQL for Data Science Coursera Week 2 Quiz Answers

Quiz 01: Module 2 Practice Quiz

Q 1. For all the questions in this practice set, you will be using the Salary by Job Range Table. This is a single table titled: salary_range_by_job_classification. This table contains the following columns:

• SetID
• Job_Code
• Eff_Date
• Sal_End_Date
• Salary_setID
• Sal_Plan
• Grade
• Step
• Biweekly_High_Rate
• Biweekly_Low_Rate
• Union_Code
• Extended_Step
• Pay_Type

Please refer to this information to write queries to answer the questions. Are you ready to get started?

• Yes, I am ready to begin.
• No, I am not ready to begin.

Q 2. Find the distinct values for the extended step. The code has been started for you, but you will need to program the third line yourself before running the query.

Which of the following values is not a distinct value?

• 5
• 2
• 11
• 6
• 0

Q 3. Excluding $0.00, what is the minimum bi-weekly high rate of pay (please include the dollar sign and decimal point in your answer)? The code has been started for you, but you will need to add onto the last line of code to get the correct answer.

Select 
min(Biweekly_high_Rate)
From salary_range_by_job_classification

Answer:

Where Biweekly_high_Rate <> ‘$0.00’ (note: it’s not the answer ,add this line in your code to get the Answer)

Q 4. What is the maximum biweekly high rate of pay (please include the dollar sign and decimal point in your answer)? The code has been started for you, but you will need to add onto the last line of code to get the correct answer.

Answer:

comment the Answer :comment the Answer: comment the Answer :

Q 5. What is the pay type for all the job codes that start with ’03’? The code has been started for you, but you will need to program the fourth and fifth lines yourself before running the query.

Select
job_code,
pay_type

Answer:

comment the Answer:

Q 6. Run a query to find the Effective Date (eff_date) or Salary End Date (sal_end_date) for grade Q90H0? The code has been started for you, but you will need to program the third through the sixth lines yourself before running the query.

Select
grade,

Answer:

comment the Answer:

Q 7. Sort the Biweekly low rate in ascending order. There is no starter code, as you need to write and run the query on your own. Hint: there are 4 lines to run this query.

Are these values properly sorted?

Answer:

NO

Q 8. Write and run a query, with no starter code to answer this question: What Step are Job Codes 0110-0400? Hint: there are 6 lines to run this query.

Answer:

comment the Answer:

Q 9. Write and run a query, with no starter code or hints to answer this question: What is the Biweekly High Rate minus the Biweekly Low Rate for job Code 0170?

Answer:

comment the Answer:

Q 10. Write and run a query, with no starter code or hints to answer this question: What is the Extended Step for Pay Types M, H, and D?

Answer:

comment the Answer:

Q 11. Write and run a query, with no starter code or hints to answer this question: What is the step for Union Code 990 and a Set ID of SFMTA or COMMN?

Answer:

comment the Answer:

Quiz 02: Module 2 Quiz

Q 1. Filtering data is used to do which of the following? (select all that apply)

Answer:

• Helps you understand the contents of your data
• Reduce the time it takes to run the query
• Reduces the strain on the client application
• Narrows down the results of the data.
• Removes unwanted data in a calculation

Q 2. You are doing an analysis on musicians that start with the letter “K”. Select the correct query that would retrieve only the artists whose name starts with this letter.

Answer:

SELECT name
FROM Artists
WHERE name LIKE ‘K%’;

Q 3. A null and a zero value effectively mean the same thing. True or false?

• True
• False

Q 4. Select all that are true regarding wildcards (Select all that apply.)

Answer:

• Wildcards at the end of search patterns take longer to run
• Wildcards take longer to run compared to a logical operator

Q 5. Select the statements below that ARE NOT true of the ORDER BY clause (select all that apply)

Answer:

• Can be anywhere in the select statement
• Cannot sort by a column not retrieved

Q 6. Select all of the valid math operators in SQL (select all that apply).

Answer:

• – (subtraction)
• + (addition)
• / (division)
• * (multiplication)

Q 7. Which of the following is an aggregate function? (select all that apply)

Answer:

• COUNT()
• MAX()
• MIN()

Q 8. Which of the following is true of GROUP BY clauses? (Select all that apply.)

Answer:

• Every column in your select statement may/can be present in a group by clause, except for aggregated calculations.
• NULLs will be grouped together if your Group By column contains NULLs
• GROUP BY clauses can contain multiple columns

Q 9.Select the true statement below.

• WHERE filters after the data is grouped
• HAVING filters after the data is grouped.

Q 10. Which is the correct order of occurrence in a SQL statement?

• select, group by, from, where, having
• select, from, where, group by, having
• select, from, where, order by, having
• select, having, where, group by

Quiz 03: Module 2 Coding Assignment

Q 1. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks that have a length of 5,000,000 milliseconds or more.

select Count(TrackId)
from Track
where Milliseconds >= 5000000

How many tracks are returned?

Answer:

2

Q 2. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices whose total is between $5 and $15 dollars.

select count(DISTINCT i.InvoiceId)
from Invoice as i
where i.Total between 5 and 15

While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are – enter that number below.

Answer:

168

Q 3. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

select FirstName, LastName, Company, State
from Customer
where State in ('RJ', 'DF', 'AB', 'BC', 'CA', 'WA', 'NY')

What company does Jack Smith work for?

• Rogers Canada
• Apple Inc.
• Google Inc.
• Microsoft Corp

Q 4. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.

select InvoiceId, InvoiceDate, CustomerId, Total
from Invoice
where CustomerId in (56, 58) AND (Total >= 1.00 and Total <= 5.00)

What was the invoice date for invoice ID 315?

• 10-27-2012
• 12-22-2013
• 1-29-2013
• 6-12-2010

Q 5. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks whose name starts with ‘All’.

select t.Name, COUNT(t.Name)
from Track t
where t.Name like 'All%'

While only 10 records are shown, the query will indicate how many total records there are for this query – enter that number below.

Answer:

15

Q 6. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customer emails that start with “J” and are from gmail.com.

select c.Email
from Customer c
where c.Email like 'J%gmail.com'

Enter the one email address returned (you will likely need to scroll to the right) below.

Answer:

[email protected]

Q 7. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.

select i.InvoiceId, i.Total
from Invoice i
where i.BillingCity in ('Brasilia', 'Edmonton', 'Vancouver')
order by i.InvoiceId DESC

What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer

Answer:

13.86

Q 8. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

select i.CustomerId , COUNT(i.InvoiceId)
from Invoice i
group by i.CustomerId
order by COUNT(i.InvoiceId) DESC

What is the number of items placed for the 8th person on this list? Enter that number below.

Answer:

7

Q 9. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find the albums with 12 or more tracks.

select t.TrackId, t.AlbumId
from Track t
group by t.AlbumId
having COUNT(DISTINCT t.TrackId) >= 12;

While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.

Answer:

158

SQL for Data Science Coursera Week 3 Quiz Answers

Quiz 01: Practice Quiz – Writing Queries

Q 1. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

How many albums does the artist Led Zeppelin have?

Answer:

Comment the Answer:

Q 2. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Answer:

Comment the Answer

Q 3. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?

Answer:

NO

Q 4. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Find the total price for each album.

Answer:

Comment the Answer.

Q 5 .All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

How many records are created when you apply a Cartesian join to the invoice and invoice items table?

Only 25 records will be shown in the output so please look at the bottom of the output to see how many records were retrieved.

Answer:

Comment the Answer.

Quiz 02: Module 3 Quiz

Q 1. Which of the following statements is true regarding subqueries?

• Subqueries always process the innermost query first and the work outward.
• Subqueries always process the outermost query first and the work inward.
• Subqueries will process whichever query you indicate for them to process first.

Q 2. If you can accomplish the same outcome with a join or a subquery, which one should you always choose?

• Joins are usually faster, but subqueries can be more reliable, so it depends on your situation.
• Whichever one you understand better and can write faster.
• A subquery because they are always faster
• A join because they are always faster

Q 3.The following diagram is a depiction of what type of join?

• Right Join
• Left Join
• Inner Join
• Full Outer Join

Q 4. Select which of the following statements are true regarding inner joins. (Select all that apply)

Answer:

• There is no limit to the number of table you can join with an inner join.
• Performance will most likely worsen with the more joins you make
• Inner joins are one of the most popular types of joins use

Q 5. Which of the following is true regarding Aliases? (Select all that apply.)

Answer:

• Aliases are often used to make column names more readable.
• SQL aliases are used to give a table, or a column in a table, a temporary name.
• An alias only exists for the duration of the query.

Q 6. What is wrong with the following query?

SELECT Customers.CustomerName, Orders.OrderID
FROM LEFT JOIN ON Customers.CustomerID = Orders.CustomerID FROM Orders AND Customers
ORDER BY
CustomerName;

• Column names do not have an alias
• The table name comes after the join condition
• Should be using an inner join rather than a left join

Q 7. What is the difference between a left join and a right join?

• A right join is always used before a full outer join, whereas a left join is always used after a full outer join
• A left join always is used before a right join in a query statement
• There is actually no difference between a left and a right join.
• The only difference between a left and right join is the order in which the tables are relating.

Q 8. If you perform a cartesian join on a table with 10 rows and a table with 20 rows, how many rows will there be in the output table?

• 200
• 10
• 15
• 20

Q 9. Which of the following statements about Unions is true? (select all that apply)

Answer.

• Each SELECT statement within UNION must have the same number of columns
• The UNION operator is used to combine the result-set of two or more SELECT statements
• The columns must also have similar data types

Q 10. Data scientists need to use joins in order to: (select the best answer)

• Retrieve data from multiple tables.
• Filter data from multiple tables.
• Create new tables.

Quiz 03: Module 3 Coding Assignment

Q 1. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Using a subquery, find the names of all the tracks for the album “Californication”.

select t.Name
from Tracks t
where t.AlbumId = ( select a.AlbumId
from Albums a
where a.Title = 'Californication')

What is the title of the 8th track?

Answer:

Porcelain

Q 2. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the total number of invoices for each customer along with the customer’s full name, city and email.

select c.CustomerId, c.FirstName, c.LastName, c.City, c.Email, COUNT(i.InvoiceId
from Customers c join Invoices i
on c.CustomerId = i.CustomerId
Group by c.CustomerId

After running the query described above, what is the email address of the 5th person, František Wichterlová? Enter the answer below (feel free to copy and paste).

Answer:

f[email protected]

Q 3. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Retrieve the track name, album, artistID, and trackID for all the albums.

select t.Name, a.Title, ar.Name, t.TrackId
from Artists ar
inner join Albums a
on ar.ArtistId = a.ArtistId
inner join Tracks t
on a.AlbumId = t.AlbumId

What is the song title of trackID 12 from the “For Those About to Rock We Salute You” album? Enter the answer
below.

Answer:

Breaking The Rules

Q 4. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Retrieve a list with the managers last name, and the last name of the employees who report to him or her.

select mgr.LastName Manager, e.LastName Employee
from Employees e
left join Employees mgr
on e.ReportsTo = mgr.EmployeeId

After running the query described above, who are the reports for the manager named Mitchell (select all that apply)?

Answer:

• Callahan
• King

Q 5. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the name and ID of the artists who do not have albums.

select a.Title, ar.Name, ar.ArtistId
from Artists ar
left join Albums a
on ar.ArtistId = a.ArtistId
where a.Title is NULL

After running the query described above, two of the records returned have the same last name. Enter that name below.

Answer:

Gilberto

Q 6. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Use a UNION to create a list of all the employee’s and customer’s first names and last names ordered by the last name in descending order.

select e.FirstName, e.LastName
from Employees e
UNION
select c.FirstName, c.LastName
from Customers c
order by c.LastName DESC

After running the query described above, determine what is the last name of the 6th record? Enter it below. Remember to order things in descending order to be sure to get the correct answer.

Answer:

Taylor

Q 7. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

See if there are any customers who have a different city listed in their billing city versus their customer city.

select c.CustomerId, c.FirstName, c.LastName, c.City
from Customers c
join Invoices i
on c.CustomerId = i.CustomerId
where c.City <> i.BillingCity

• No customers have a different city listed in their billing city versus customer city.
• 3 customers have a different city listed in their billing city versus customer city.
• 8 customers have a different city listed in their billing city versus customer city.
• 17 customers have a different city listed in their billing city versus customer city.

SQL for Data Science Coursera Week 4 Quiz Answers

Quiz 01: Module 4 Quiz

Q 1. Which of the following are supported in SQL when dealing with strings? (Select all that apply)

Answer:

• Trim
• Concatenate
• Lower
• Substring
• Upper

Q 2. What will the result of the following statement be?

SELECT SUBSTR('You are beautiful.', 3)

• u are beautiful.
• beautiful.
• You are beautiful.
• This will return an error

Q 3. What are the results of the following query?

select * orders
where order_date = ‘2017-07-15’

Additional information:

• Orders = integer
• Order_date = datetime

Answer:

• You will get all the orders with an order date of 2017-07-15.
• You will get all of the orders.
• You won’t get any results.

Q 4. Case statements can only be used for which of the following statements (select all that apply)?

Answer:

• Update
• Delete
• Insert
• Select

Q 5. Which of the following is FALSE regarding views?

• Views will remain after the database connection has ended
• Views are stored in a query
• Views can be used to encapsulate queries

Q 6. You are only allowed to have one condition in a case statement. True or false?

Answer:

False

Q 7. Select the correct SQL syntax for creating a view.

Answer:

CREATE VIEW
customers AS
SELECT * 
FROM customers
WHERE Name LIKE '%I'

Q 8. Profiling data is helpful for which of the following? (Select all that apply)

Answer:

• Understanding your data
• Filter out unwanted data elements

Q 9. What is the most important step before beginning to write queries?

• Deciding what tables you want to join
• Deciding what should be done on the client application vs the RDMS
• Understanding your data

Q 10. When debugging a query, what should you always remember to do first?

• Start simple and break it down first
• Start with the inner most query
• Make sure you didn’t miss any commas.
• Start by examining the joins

Quiz 02: Module 4 Coding Questions

Q 1. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE. (e.g. LOS ANGELES USA)

select c.CustomerId,
c.FirstName || ' ' || c.LastName as Full_Name,
c.Address,
UPPER(c.City || ' ' || c.Country)
from Customer c

Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE. (e.g. LOS ANGELES USA)

What is the city and country result for CustomerID 16?

Answer:

MOUNTAIN VIEW USA

Q 2. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Create a new employee user id by combining the first 4 letters of the employee’s first name with the first 2 letters of the employee’s last name. Make the new field lower case and pull each individual step to show your work.

select c.FirstName,
substr(c.FirstName, 1,4) as FirstNameShort,
c.LastName,
substr(c.LastName,1,2) as LastNameShort,
LOWER(substr(c.FirstName,1,4) || substr(c.LastName,1,2)) as NewID
from Customers c

What is the final result for Robert King?

Answer:

RobeKi

Q 3. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Show a list of employees who have worked for the company for 15 or more years using the current date function. Sort by lastname ascending.

1	select e.LastName,e.FirstName,e.BirthDate,DATE('now') - e.BirthDate as Age,DATE('n
2	from Employees e
3	where Tenure >= 15	Run
4	order by e.LastName asc

What is the lastname of the last person on the list returned?

Answer:

Peacock

Q 4. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Profiling the Customers table, answer the following question.

select *
from Customers c
where c.Company	

is null

Are there any columns with null values? Indicate any below. Select all that apply.

Answer:

• Fax
• Company
• Postal Code

Q 5. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the cities with the most customers and rank in descending order.

1	select c.City, count(c.CustomerId)
2	from Customers c
3	group by c.City	Run
4	order by count(c.CustomerId) DESC

Which of the following cities indicate having 2 customers?

Answer:

• London
• Mountain View
• São Paulo

Q 6. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Create a new customer invoice id by combining a customer’s invoice id with their first and last name while ordering your query in the following order: firstname, lastname, and invoiceID.

1	select  c.FirstName  ||  c.LastName  ||   i.InvoiceId
2	from Customers c
3	left join Invoices i
4	on c.CustomerId = i.CustomerId
5	where NewInvoiceId like 'AstridGruber%'
6	order by c.FirstName, c.LastName,  i.InvoiceId	as	NewInvoiceId

Select all of the correct “AstridGruber” entries that are returned in your results below. Select all that apply.

Answer:

• AstridGruber273
• AstridGruber296
• AstridGruber370

Get All Course Quiz Answers of Learn SQL Basics for Data Science Specialization

SQL for Data Science Coursera Quiz Answers

Data Wrangling, Analysis and AB Testing with SQL Coursera Quiz Answers

Distributed Computing with Spark SQL Coursera Quiz Answers

Error handling in SQL Server breaks down into two very distinct situations: you’re handling errors because you’re in SQL Server 2005 or you’re not handling errors because you’re in SQL Server 2000. What’s worse, not all errors in SQL Server, either version, can be handled. I’ll specify where these types of errors come up in each version.

The different types of error handling will be addressed in two different sections. ‘ll be using two different databases for the scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks] for SQL Server 2005.

I’ve broken down the scripts and descriptions into sections. Here is a Table of Contents to allow you to quickly move to the piece of code you’re interested in. Each piece of code will lead with the server version on which it is being run. In this way you can find the section and the code you want quickly and easily.

As always, the intent is that you load this workbench into Query Analyser or Management Studio and try it out for yourself! The workbench script is available in the downloads at the bottom of the article.

• GENERATING AN ERROR
• SEVERITY AND EXCEPTION TYPE
• TRAP AN ERROR
• USING RAISERROR
• RETURNING ERROR CODES FROM STORED PROCEDURES
• TRANSACTIONS AND ERROR TRAPPING
• EXTENDED 2005 ERROR TRAPPING

SQL Server 2000 – GENERATING AN ERROR

SQL Server 2005 – GENERATING AN ERROR

SQL Server 2000 AND 2005 – ERROR SEVERITY AND EXCEPTION TYPE

The error message provides several pieces of information:

Msg

A message number identifies the type fo error. Can up to the value of 50000. From that point forward custom user defined error messages can be defined.

Level

The severity level of the error.

• 10 and lower are informational.
• 11-16 are errors in code or programming, like the error above.
• Errors 17-25 are resource or hardware errors.
• Any error with a severity of 20 or higher will terminate the connection (if not the server).

Line

Defines which line number the error occurred on and can come in extremely handy when troubleshooting large scripts or stored procedures.

Message Text

The informational message returned by SQL Server.

Error messages are defined and stored in the system table sysmessages.

SQL Server 2000 – CATCH AN ERROR

SQL Server 2000 does not allow us to stop this error being returned, but we can try to deal with it in some fashion. The core method for determining if a statement has an error in SQL Server 2000 is the @@ERROR value. When a statement completes, this value is set.

If the value equals zero(0), no error occured. Any other value was the result of an error.

The following TSQL will result in the statement ‘A constraint error has occurred’ being printed,as well as the error.

@@ERROR is reset by each and every statement as it occurs. This means that if we use the exact same code as above, but check the @@ERROR function a second time, it will be different.

You will see the error number as returned by the @@ERROR statement as being zero(0), despite the fact that we just had a clearly defined error.

The problem is, while the UPDATE statement did in fact error out, the IF statement executed flawlessly and @@ERROR is reset after each and every statement in SQL Server.

In order to catch and keep these errors, you need to capture the @@ERROR value after each execution.

Now we can capture the error number and refer to it as often as needed within the code.

SQL Server 2005 – CATCH AN ERROR

While @@ERROR is still available in SQL Server 2005, a new syntax has been added to the T-SQL language, as implemented by Microsoft: TRY... CATCH.

This allows us to finally begin to perform real error trapping.

While there is an error encountered in the code, none is returned to the calling function. In fact, all that will happen in this case is the string 'Error Handled' is returned to the client.

We have actually performed the function of error trapping within TSQL.

There are a number of issues around the use of TRY...CATCH that have to be dealt with, which we will cover later. For example, simply having a TRY...CATCH statement is not enough.

Consider this example:

The second error is handled, but the first one is not and we would see this error returned to client application:

To eliminate this problem place multiple statements within the TRY statement.

SQL Server 2000 – USING RAISERROR

The RAISERROR function is a mechanism for returning to calling applications errors with your own message. It can use system error messages or custom error messages. The basic syntax is easy:

To execute RAISERROR you’ll either generate a string, up to 400 characters long, for the message, or you’ll access a message by message id from the master.dbo.sysmessages table.

You also choose the severity of the error raised. Severity levels used in RAISERROR will behave exactly as if the engine itself had generated the error. This means that a SEVERITY of 20 or above will terminate the connection. The last number is an arbitrary value that has to be between 1 and 127.

You can format the message to use variables. This makes it more useful for communicating errors:

You can use a variety of different variables. You simply have to declare them by data type and remember that, even with variables, you have a 400 character limit. You also have some formatting options.

A few notes about severity and status. Status can be any number up to 127 and you can make use of it on your client apps. Setting the Status to 127 will cause ISQL and OSQL to return the error number to the operating environment.

SQL SERVER 2005 – USING RAISERROR

The function of RAISERROR in SQL Server 2005 is largely the same as for SQL 2000. However, instead of 400 characters, you have 2047. If you use 2048 or more, then 2044 are displayed along with an ellipsis.

RAISERROR will cause the code to jump from the TRY to the CATCH block.

Because of the new error handling capabilities, RAISERROR can be called in a more efficient manner in SQL Server 2005. This from the Books Online:

SQL Server 2000 – RETURNING ERROR CODES FROM STORED PROCEDURES

Stored procedures, by default, return the success of execution as either zero or a number representing the failure of execution, but not necessarily the error number encountered.

This will cause an error and the SELECT statement will return a non-zero value. On my machine, -6. In order take control of this, modify the procedure as follows:

This time the SELECT @err statement will return the 547 error number in the results. With that, you can begin to create a more appropriate error handling routine that will evolve into a coding best practice within your organization.

SQL Server 2005 – RETURNING ERROR CODES FROM STORED PROCEDURES

In order to appropriately handle errors you to know what they are. You may also want to return the errors to the calling application. A number of new functions have been created so that you can appropriately deal with different errors, and log, report, anything you need, the errors that were generated.

When you run the code above, you should receive this on the client, in the message, with a non-zero number in the result set:

In other words, everything you need to actually deal with errors as they occur.

You’ll also notice that the procedure returned an error value (non-zero) even though we didn’t specify a return code. You can still specify a return value as before if you don’t want to leave it up to the engine.

SQL Server 2000 – TRANSACTIONS AND ERROR TRAPPING

The one area of control we do have in SQL Server 2000 is around the transaction. In SQL Server 2000 you can decide to rollback or not, those are your only options. You need to make decision regarding whether or not to use XACT_ABORT. Setting it to ON will cause an entire transaction to terminate and rollback in the event of any runtime error. if you set it to OFF, then in some cases you can rollback the individual statement within the transaction as opposed to the entire transaction.

Modify the procedure to handle transactions:

Since the above code will generate an error on the second statement, the transaction is rolled back as a unit. Switch to the results in order to see that the zip code is, in fact, still 90210. If we wanted to control each update as a seperate statement, in order to get one of them to complete, we could encapsulate each statement in a transaction:

In this case then, the return value will be ‘90210’ since the first update statement will complete successfully. Be sure that whatever mechanism you use to call procedures does not itself begin a transaction as part of the call or the error generated will result in a rollback, regardless of the commit within the procedure. In the next example, we’ll create a transaction that wraps the other two transactions, much as a calling program would. If we then check for errors and commit or rollback based on the general error state, it’s as if the inner transaction that was successful never happened, as the outer transaction rollback undoes all the work within it.

SQL Server 2005 – TRANSACTIONS AND ERROR TRAPPING

The new error handling changes how transactions are dealt with. You can now check the transaction state using XACT_STATE() function. Transactions can be:

• Closed (equal to zero (0))
• Open but unable to commit (-1)
• Open and able to be committed (1)

From there, you can make a decision as to whether or not a transaction is committed or rolled back. XACT_ABORT works the same way.

SQL Server 2005 – EXTENDED 2005 ERROR TRAPPING

With the new TRY...CATCH construct, it’s finally possible to do things about errors, other than just return them. Take for example the dreaded deadlock. Prior to SQL Server 2005, the best you could hope for was to walk through the error messages stored in the log recorded by setting TRACEFLAG values. Now, instead, you can set up a retry mechanism to attempt the query more than once.

This is the first article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Exception Handling Basics

If we are not clear about the basics of Exception handling in Sql Server, then it is the most complex/confusing task, it will become nightmarish job to identify the actual error and the root cause of the unexpected results. In this blog post I will try to make sure that all the concepts are cleared properly and the one who goes through it should feel the sense of something they have learnt new and feel themselves an expert in this area. And at the end of the blog post will present the ideal exception handling template which one should be using for proper error handling in Sql Server.

Last week on 11th January, 2014, I have presented a session on this topic at Microsoft Office in the Sql Bangalore User Group meeting which is attend by hundreds of enthusiastic Sql Server working professionals. Received very good feedback and few messages posted in the Facebook SQLBangalore user group were “Thanks Basavaraj Biradar! Your session was divine!” By Community Member Adarsh Prasad, “Thanks Basavaraj for your excellent session” By Community Member Selva Raj. Enough Self Praise, enough expectation is set, let’s cut short the long story short and move onto the deep dive of Exception handling basics

In this topic will cover the following concepts with extensive list of examples

• Error Message
• Error Actions

Let’s start with a simple statement like below which results in an exception as I am trying to access a non-existing table.

--------------Try To Access Non-Existing Table ---------------
 SELECT * FROM dbo.NonExistingTable
 GO

Result of the above query:
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘dbo.NonExistingTable’.

By looking at the above error message, we can see that the error message consists of following 5 parts:

Msg 208 – Error Number
Level 16 – Severity of the Error
State 1 – State of the Error
Line 2 – Line Number of the statement which generated the Error
Invalid object name ‘dbo.NonExistingTable’. – Actual Error Message

Now Let’s wrap the above statement which generated the error into a stored procedure as below

---Create the Stored Procedure 
CREATE PROCEDURE dbo.ErrorMessageDemo
AS 
BEGIN
	SELECT * FROM dbo.NonExistingTable
END
GO
--Execute the Stored Procedure
EXEC dbo.ErrorMessageDemo
GO

Result of executing the above stored procedure is:
Msg 208, Level 16, State 1, Procedure ErrorMessageDemo, Line 4
Invalid object name ‘dbo.NonExistingTable’.

If we compare this error message with the previous error message, then this message contains one extra part “Procedure ErrorMessageDemo“ specifying the name of the stored procedure in which the exception occurred.

Parts of ErrorMessage

The below image explains in detail each of the six parts of the error message which we have identified just above:

In case the image is not clear below is the detail which I have tried to present in it:

ERROR NUMBER/ Message Id:

Any error number which is <= 50000 is a System Defined Messages and the ones which are > 50000 are User Defined Messages. SYS.Messages catalog view can be used to retrieve both System and User Defined Messages. We can add a user defined message using sp_addmessage and we can remove it using the system stored procedure sp_dropmessage.

ERROR SEVERITY: Error Severity can be between 0-25.

0-10:  Informational or a warning
11-16: Programming Errors
17-25: Resource / Hardware / OS/ Sql Server Internal Errors
20-25: Terminates the Connection
19-25: Only User with SysAdmin rights can raise error’s with this severity

ERROR STATE: Same Error can be raised for several different conditions in the code. Each specific condition that raises the error assigns a unique state code. Also the SQL Support Team uses it to find the location in the source code where that error is being raised.

ERROR PROCEDURE: Name of the Stored Procedure or the Function in which the Error Occurred. It Will be blank if it is a Normal Batch of Statement.

ERROR LINE: Line Number of the Statement within SP/ UDF/ Batch which triggered the error. It will be 0 If SP/UDF Invoke Causes the Error.

ERROR MESSAGE: Error description detailing out the reason for the error

Now let us see how Sql Server Reacts to different errors. To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo
CREATE DATABASE SqlHintsErrorHandlingDemo
GO
USE SqlHintsErrorHandlingDemo
GO
CREATE TABLE dbo.Account
(	
 AccountId INT NOT NULL PRIMARY KEY, 
 Name	 NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL CHECK (Balance>=0)	
)
GO

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

Let us first check whether we are able to insert valid Account into the Account table.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

Result: We are able to successfully insert a record in the Account table

Now try to insert one more account whose AccountId is same as the one which we have just inserted above.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)

Result: It fails with below error message, because we are trying to insert a duplicate value for the the Primary Key column AccountId.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK__Account__349DA5A67ED5FC72’. Cannot insert duplicate key in object ‘dbo.Account’. The duplicate key value is (1).
The statement has been terminated.

Let me empty the Account Table by using the below statement:

DELETE FROM dbo.Account

DEMO 1: Now let us see what will be the result if we execute the below batch of Statements:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2', 20000)
GO

Result: The First and Third Insert statements in the batch are succeeded even though the Second Insert statement fails

From the above example result it is clear that even though the Second insert statement is raising a primary key voilation error, Sql server continued the execution of the next statement and it has successfully inserted the Account with AccountId 2 by the third Insert statement.

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO:

DELETE FROM dbo.Account

DEMO 2: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is the first line i.e. SET XACT_ABORT ON:

SET XACT_ABORT ON
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1',  10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the first Insert succeeded

From the above example result it is clear that failure in the Second insert statement due to primary key violation caused Sql Server to terminate the execution of the Subsequent statements in the batch.

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion.

The Only difference in the DEMO 2 script from DEMO 1 is the additional first statement SET XACT_ABORT ON. So from the result it is clear that the SET XACT_ABORT ON statement is causing Sql Server to do the Batch Abortion for a Statement Termination Error. It means SET XACT_ABORT ON converts the Statement Terminating errors to the Batch Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 3: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is that the INSERT statements are executed in a Transaction:

BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: Same as the DEMO 1, that is only the statement which raised the error is terminated but continues with the next statement in the batch. Here First and Third Inserts are Successful even though the Second statement raised the error.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 4: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 2 is that the INSERT statement’s are executed within a Transaction

SET XACT_ABORT ON
BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: No records inserted

From the above example result it is clear that SET XACT_ABORT ON setting not only converts the Statement Termination Errors to the Batch Abortion Errors and also ROLLS BACK any active transactions started prior to the BATCH Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 5: As a part of this DEMO we will verify what happens if a CONVERSION Error occurs within a batch of statement.

CONVERSION ERROR: Trying to convert the string ‘TEN THOUSAND’ to MONEY Type will result in an error. Let us see this with an example:

SELECT CAST('TEN THOUSAND' AS MONEY)

RESULT:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

Now let us see what happens if we come across such a CONVERSION error within a batch of statement like the below one:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

UPDATE dbo.Account 
SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
WHERE AccountId = 1

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the First INSERT is successful

From the above result it is clear that CONVERSION errors cause the BATCH abortion, i.e Sql Server terminates the statement which raised the error and the subsequent statements in the batch. Where as PRIMARY KEY violation was resulting in a Statement Termination as explained in the DEMO 1.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 6: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the previous DEMO 5 is that the Batch statement’s are executed within a Transaction

BEGIN TRAN 
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1', 10000)

 UPDATE dbo.Account 
 SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
 WHERE AccountId = 1

 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN 
GO

RESULT: No records inserted

From the above example result it is clear that CONVERSION errors results in a BATCH Abortion and BATCH Abortion errors ROLLS BACK any active transactions started prior to the BATCH Abortion error.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

Enough examples, let me summarize the Sql Server Error Actions. Following are the four different ways Sql Server responds(i.e. Error Actions) in response to the errors:

• Statement Termination
• Scope Abortion
• Batch Abortion
• Connection Termination

Many of these error actions I have explained in the above DEMOs using multiple examples. To explain these error actions further let us take a scenario as shown in the below image, in this scenario from client system an Execution request for the MainSP is submitted and the MainSP internally calls to sub sp’s SubSP1 and SubSP2 one after another:

Statement Termination :

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates only the statement that raised the error i.e. Statement-1 but continues executing subsequent statements in the SubSP1 and MainSP calls the subsequent SP SubSp2.

Scope Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the same scope, but continues to execute all the Statements outside the scope of the statement which raised the error. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1, but continues executing further all the statements/Sub Sp’s (For Example SubSP2) in the MainSP.

Let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

-------------Scope Abortion Demo-------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	--Try to access Non-Existent Table
	SELECT * FROM NonExistentTable
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END

GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP'
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP'
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:

From the above SP execution results it is clear that the Access for a non existent table NonExistentTable from SubSP1 is not only terminating the statement which try’s to access this NonExistentTable table, but also the Subsequent statements in the SubSP1’s scope. But Sql Server continues with the execution of the subsequent statements which are present in the in the MainSP which has called this SubSP1 and also the SubSP2 is called from the MainSP.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Batch Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. Batch Abortion Errors ROLLS BACK any active transactions started prior to the statement which causes BATCH Abortion error.

We have already seen multiple Batch Abortion examples in the above DEMOs. Here let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

------------Batch Abortion Demo --------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	PRINT CAST('TEN THOUSAND' AS MONEY)
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END
GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP '
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP '
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:

From the above SP execution results it is clear that the CONVERSION/CAST statement in the SubSP1 is causing the Batch Abortion.It is not only terminating the statement which raised the error but all the subsequent statement in the SubSP1 and the further statement in the MainSP which has called this SubSP1 and also the SubSP2 is not called from the MainSP post this error.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Connection Termination :

Errors with severity level 20-25 causes the Connection Termination. Only User with SysAdmin rights can raise error’s with these severity levels. As shown in the below image the Statement-1 in SubSP1 is causing an error with severity 20-25, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. And finally terminates the connection. Note if there are any active Transactions which are started prior to the statement which caused the Connection Termination error, then Sql Server Takes care of Rolling Back all such transactions.

If we use RaiseError with WITH LOG option to raise an exception with severity level >=20 will result in a connection termination. Let us execute the below statement and observe the result:

RAISERROR('Connection Termination Error Demo', 20,1) WITH LOG
GO

RESULT: Connection is Terminated

Below query gives the list of Error’s that cause the Connection Termination.

SELECT * FROM sys.messages 
WHERE severity >= 20 and language_id =1033

Clean-UP:
Let us drop the database which we have created for this demo

--Drop the Database SqlHintsErrorHandlingDemo
USE TempDB
GO
DROP DATABASE SqlHintsErrorHandlingDemo

Let us know your feedback on this post, hope you have learnt something new. Please correct me if there are any mistakes in this post, so that I can correct it and share with the community.

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
RAISERROR (Transact-SQL)	RAISERROR (Transact-SQL)	rwestMSFT	randolphwest	08/09/2022	sql	t-sql	reference	RAISERROR RAISERROR_TSQL RAISEERROR_TSQL	sysmessages system table errors [SQL Server], RAISERROR statement user-defined error messages [SQL Server] system flags generating errors [SQL Server] TRY block [SQL Server] recording errors ad hoc messages RAISERROR statement CATCH block messages [SQL Server], RAISERROR statement	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]

[!NOTE]
The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view, or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applications should use THROW instead.

:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions

Syntax

Syntax for SQL Server and Azure SQL Database:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { , severity, state }
    [ , argument [ , ...n ] ] )
    [ WITH option [ , ...n ] ]

Syntax for Azure Synapse Analytics and Parallel Data Warehouse:

RAISERROR ( { msg_str | @local_variable }
    { , severity, state }
    [ , argument [ , ...n ] ] )
    [ WITH option [ , ...n ] ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

msg_id

A user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_str

A user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. This storage requirement decreases the number of available characters for message output.

When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type

The parameters that can be used in msg_str are:

flag

A code that determines the spacing and justification of the substituted value.

Code	Prefix or justification	Description
— (minus)	Left-justified	Left-justify the argument value within the given field width.
+ (plus)	Sign prefix	Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero)	Zero padding	Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number)	0x prefix for hexadecimal type of x or X	When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank)	Space padding	Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

An integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

The maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specification	Represents
d or i	Signed integer
o	Unsigned octal
s	String
u	Unsigned integer
x or X	Unsigned hexadecimal

These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to [!INCLUDEtsql] data types, while the specifications used in printf map to C language data types. Type specifications used in printf are not supported by RAISERROR when [!INCLUDEtsql] does not have a data type similar to the associated C data type. For example, the %p specification for pointers is not supported in RAISERROR because [!INCLUDEtsql] does not have a pointer data type.

To convert a value to the [!INCLUDEtsql] bigint data type, specify %I64d.

@local_variable

Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.

severity

The user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.

For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

[!CAUTION]
Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

You can specify -1 to return the severity value associated with the error as shown in the following example.

RAISERROR (15600, -1, -1, 'mysp_CreateCustomer');

[!INCLUDEssResult]

Msg 15600, Level 15, State 1, Line 1
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.

state

An integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.

If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

argument

The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

option

A custom option for the error and can be one of the values in the following table.

Value	Description
LOG	Logs the error in the error log and the application log for the instance of the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssDE]. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG. [!INCLUDEapplies] [!INCLUDEssNoVersion]
NOWAIT	Sends messages immediately to the client. [!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]
SETERROR	Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level. [!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]

Remarks

The errors generated by RAISERROR operate the same as errors generated by the [!INCLUDEssDE] code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

• Outside the scope of any TRY block.

• With a severity of 10 or lower in a TRY block.

• With a severity of 20 or higher that terminates the database connection.

CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.

When msg_id specifies a user-defined message available from the sys.messages catalog view, RAISERROR processes the message from the text column using the same rules as are applied to the text of a user-defined message specified using msg_str. The user-defined message text can contain conversion specifications, and RAISERROR will map argument values into the conversion specifications. Use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.

RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the [!INCLUDEtsql] PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

Typically, successive arguments replace successive conversion specifications; the first argument replaces the first conversion specification, the second argument replaces the second conversion specification, and so on. For example, in the following RAISERROR statement, the first argument of N'number' replaces the first conversion specification of %s; and the second argument of 5 replaces the second conversion specification of %d.

RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
-- The message text returned is: This is message number 5.
GO

If an asterisk (*) is specified for either the width or precision of a conversion specification, the value to be used for the width or precision is specified as an integer argument value. In this case, one conversion specification can use up to three arguments, one each for the width, precision, and substitution value.

For example, both of the following RAISERROR statements return the same string. One specifies the width and precision values in the argument list; the other specifies them in the conversion specification.

RAISERROR (N'<<%*.*s>>', -- Message text.
           10, -- Severity,
           1, -- State,
           7, -- First argument used for width.
           3, -- Second argument used for precision.
           N'abcde'); -- Third argument supplies the string.
-- The message text returned is: <<    abc>>.
GO
RAISERROR (N'<<%7.3s>>', -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

Permissions

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

Examples

A. Returning error information from a CATCH block

The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked the CATCH block.

[!NOTE]
RAISERROR only generates errors with state from 1 through 127. Because the [!INCLUDEssDE] may raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

B. Creating an ad hoc message in sys.messages

The following example shows how to raise a message stored in the sys.messages catalog view. The message was added to the sys.messages catalog view by using the sp_addmessage system stored procedure as message number 50005.

EXEC sp_addmessage @msgnum = 50005,
              @severity = 10,
              @msgtext = N'<<%7.3s>>';
GO
RAISERROR (50005, -- Message id.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO
EXEC sp_dropmessage @msgnum = 50005;
GO

C. Using a local variable to supply the message text

The following code example shows how to use a local variable to supply the message text for a RAISERROR statement.

DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'<<%7.3s>>';

RAISERROR (@StringVariable, -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

See also

• Built-in Functions (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• PRINT (Transact-SQL)
• sp_addmessage (Transact-SQL)
• sp_dropmessage (Transact-SQL)
• sys.messages (Transact-SQL)
• xp_logevent (Transact-SQL)
• @@ERROR (Transact-SQL)
• ERROR_LINE (Transact-SQL)
• ERROR_MESSAGE (Transact-SQL)
• ERROR_NUMBER (Transact-SQL)
• ERROR_PROCEDURE (Transact-SQL)
• ERROR_SEVERITY (Transact-SQL)
• ERROR_STATE (Transact-SQL)
• TRY…CATCH (Transact-SQL)