Вывод ошибок mssql

This article explores the SQL Server PRINT statements, and its alternative SQL Server RAISEERROR statements to print
messages in a query.

Introduction

Suppose you execute a query with multiple steps. We want to get a message once each step completes. It helps to track the query progress. Usually, we use the SQL PRINT statement to print corresponding messages or track the variable values while query progress.

We also use interactions or multiple loops in a query with a while or for a loop. We can also use the SQL PRINT
statement to track the iteration.

We use the SQL Server PRINT statement to return messages to the client. We specify the message as string expressions input. SQL Server returns the message to the application.

In this article, we will explore several use cases of SQL PRINT statement, its limitations, and alternatives of SQL
PRINT statements.

Example 1: SQL Server PRINT statement to print a string

It is the simplest example. Execute the following query in SSMS, and it returns the following message in the output:

In SSMS, we get the PRINT statement output in SSMS message window as shown in the above image.

Example 2: PRINT statement to print a variable value

We can use the SQL PRINT statement to print a variable value as well. Let’s define a message in a variable and later
print this message:

Example 3: SQL Server PRINT statement to print an integer value

We can use the SQL PRINT statement to print an integer value, as shown below:

We can specify only CHAR, NCHAR, VARCHAR or NVARCHAR data types in the PRINT statement. In this case, it implicitly converts an integer value to the VARCHAR data type internally.

Let’s use another example with an integer variable and a string in the PRINT statement. You get an error message in
data type conversion because SQL Server is trying to convert the varchar data type to integer. In data type
precedence, the integer data type has high precedence than the varchar data type:

We explicitly convert the integer data type to varchar using the SQL CAST statement:

We can use the SQL CONCAT function as well, and it automatically does data type conversion for us. In the following
query, we get the output using the CONCAT function similar to the CAST operator:

Example 4: SQL Server PRINT statement with XML type variable value

We can use XML data type as well with the PRINT statement, but it requires data conversion.

As shown in the following output, we cannot directly use an XML variable in the PRINT statement. It gives an error
message that implicit conversion from XML to nvarchar is not allowed:

We can use SQL CAST or CONVERT function explicitly and get the required output:

Example 5: SQL Server PRINT Statement with IF conditions

Let’s use the PRINT statement to print the message satisfied in the IF condition. In this example, the variable @a
contains a string. The IF condition checks for the string and prints message satisfying the condition:

Example 6: PRINT Statement with NULL values

We cannot print NULL in the message using the SQL PRINT statement. The following query does not return any result:

Let’s use the following query that contains a variable with NULL values. In the PRINT statement, we use a string
along with this variable, and it does not return any message. The concatenation of a string and the variable @a
(NULL) that does not return any output:

Example 7: SQL Server PRINT Statement in a WHILE loop

As stated earlier, many times, we require knowing information about each iteration when query running in a loop such as WHILE or FOR.

The following query uses WHILE loop and prints a message about each iteration:

It gives the following output. We can use a Print statement with an appropriate message and track query progress.

Limitations of SQL Server PRINT statement

In the previous example, we saw the use of cases of SQL PRINT statements. We have the following limitations with
PRINT as well:

1. We need to use CAST, CONVERT or CONCAT functions in the PRINT statement with variables
2. We cannot see a PRINT statement in SQL PROFILER
3. The PRINT statement does not return the message immediately; it buffers the output and displays them

Let’s elaborate point no 3 and see its alternatives.

Execute the following query that contains two PRINT statements. Looking at the query, you might think of output in
the following form:

• It gives the message from the first PRINT statement
• Waits for 5 seconds
• It gives the message for a second PRINT statement
• Waits for another 5 seconds

In the following GIF image, you can note that it prints message from both SQL PRINT statements together after 10
seconds:

Let’s use another example and see PRINT statement behavior. In this, we want to print the message as soon as
iteration completes:

In the output, we can note that all messages output from PRINT statements appear together once the execution
completes. The loop executes 14 times and waits 1 second on each execution. Therefore, we get output after 14
seconds for all PRINT statements:

SQL Server buffers messages for multiple PRINT statements and displays them together.

It is not the desired output, and in any case, it might not be useful as you cannot track query progress in
real-time.

SQL Server RAISERROR statement

We can use an alternative to the SQL PRINT statement that is RAISERROR.

We require a few arguments in RAISERROR statements.

• Message – It is the message that we want to print
• Severity – It is a number between 0 and 25 and defines the severity of the messages. It treats
  the message differently with each severity. We will see a few examples of it in this article
• State – It is a number between 0 and 255 to distinguish one error from another. It is good to
  use value 1 for this article

We need to use RAISERROR statement with NOWAIT clause; otherwise, it shows the same behavior as of SQL PRINT
statement:

In the following output with SQL Server RAISERROR statements, we can note the following:

• It gives the message from the first PRINT statement
• Waits for 5 seconds
• It gives the message for the second PRINT statement
• Waits for another 5 seconds

Previously we used severity 0 in the RAISERROR statement. Let’s use the same query with severity 16:

In the output, we can see the message appears in red, and it shows the message as an error instead of a regular message. You also get message id, level, and state as well:

Let’s execute the following query with severity 1 and severity 16. Severity 1 shows the message with additional information, but it does not show the message as an error. You can see the text color in black.

Another SQL Server RAISERROR shows the output message as an error:

We cannot use SQL Server RAISERROR directly using the variables. We get the following output that is not the desired
output:

It prints the message but does not show the value of the variable:

We need to use the C-style print statements with RAISERROR. The following query shows the variable with the
RAISERROR. You can notice that we use %s and %d to print a string and integer value:

We get the instant output in SQL Server RAISERROR along with WITH NOWAIT statement as per our requirement and does
not use buffer to display output once the query finishes:

You might confuse between RAISERROR statement that it is for raising error messages in SQL Server. We can use it as an alternative to the SQL PRINT statement as well. Usually, developers use PRINT statements only to gives messages in a query. You should explore RAISERROR statements for your queries, stored procedures.

Conclusion

In this article, we explored the SQL Server PRINT statement and its usages to track useful milestones in a query. We also learned the limitations of it along with alternative solution RAISERROR statement in SQL Server. SQL Server
RAISERROR gives you a great advantage to control output buffer behavior.

message_text — сообщение, которое вы хотите показать при ошибке. Замечание: вы можете добавлять пользовательские сообщения для вывода информации об ошибке. Смотрите следующий раздел статьи.
message_id — id сообщения об ошибке. Если вы хотите вывести пользовательское сообщение, вы можете определить этот идентификатор. Посмотрите список идентификаторов сообщений в sys.messages DMV.
Запрос:

select * from sys.messages

Вывод:

severity — серьезность ошибки. Тип данных переменной severity — smallint, значения находятся в диапазоне от 0 до 25. Допустимыми значениями серьезности ошибки являются:

• 0-10

— информационные сообщения

• 11-18

— ошибки

• 19-25

— фатальные ошибки

Замечание: Если вы создаете пользовательское сообщение, сложность, указанная в этом сообщении, будет перебиваться сложностью, заданной в операторе RAISERROR.
state — уникальное идентификационное число, которое может использоваться для раздела кода, вызывающего ошибку. Тип данных параметра state — smallint, и допустимые значения между 0 и 255.

Теперь давайте перейдем к практическим примерам.

Пример 1: использование оператора SQL Server RAISERROR для вывода сообщения

В этом примере вы можете увидеть, как можно отобразить ошибку или информационное сообщение с помощью оператора RAISERROR.

Предположим, что вы хотите отобразить сообщение после вставки записей в таблицу. Мы можем использовать операторы PRINT или RAISERROR. Ниже — код:

SET nocount ON 

INSERT INTO tblpatients 

            (patient_id, 

             patient_name, 

             address, 

             city) 

VALUES     ('OPD00006', 

            'Nimesh Upadhyay', 

            'AB-14, Ratnedeep Flats', 

            'Mehsana') 

RAISERROR ( 'Patient detail added successfully',1,1) 

Вывод:

Как видно на рисунке выше, ID сообщения равно 50000, поскольку это пользовательское сообщение.

Пример 2: оператор SQL RAISERROR с текстом динамического сообщения

Теперь посмотрите, как мы можем создать текст динамического сообщения для оператора SQL RAISERROR.

Предположим, что мы хотим напечатать в сообщении ID пациента. Я описал локальную переменную с именем @PatientID, которая содержит patient_id. Чтобы отобразить значение переменной @PatientID в тексте сообщения, мы можем использовать следующий код:

DECLARE @PatientID VARCHAR(15) 

DECLARE @message NVARCHAR(max) 

SET @PatientID='OPD00007' 

SET @message ='Patient detail added successfully. The OPDID is %s' 

INSERT INTO tblpatients 

            (patient_id, 

             patient_name, 

             address, 

             city) 

VALUES     ('' + @PatientID + '', 

            'Nimesh Upadhyay', 

            'AB-14, Ratnedeep Flats', 

            'Mehsana') 

RAISERROR ( @message,1,1,@patientID) 

Вывод:

Для отображения строки в операторе RAISERROR, мы должны использовать операторы print в стиле языка Си.

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

Использование SQL RAISERROR в блоке TRY..CATCH

В этом примере мы добавляем SQL RAISERROR в блок TRY. При запуске этого кода он выполняет связанный блок CATCH. В блоке CATCH мы будем выводить подробную информацию о возникшей ошибке.

BEGIN try 

    RAISERROR ('Error invoked in the TRY code block.',16,1 ); 

END try 

BEGIN catch 

    DECLARE @ErrorMsg NVARCHAR(4000); 

    DECLARE @ErrSeverity INT; 

    DECLARE @ErrState INT; 

    SELECT @ErrorMsg = Error_message(), 

           @ErrSeverity = Error_severity(), 

           @ErrState = Error_state(); 

    RAISERROR (@ErrorMsg,

               @ErrSeverity,

               @ErrState 

    ); 

END catch;

Так мы добавили оператор RAISERROR с ВАЖНОСТЬЮ МЕЖДУ 11 И 19. Это вызывает выполнение блока CATCH.

В блоке CATCH мы показываем информацию об исходной ошибке, используя оператор RAISERROR.
Вывод:

Как вы можете увидеть, код вернул информацию об исходной ошибке.

Теперь давайте разберемся, как добавить пользовательское сообщение, используя хранимую процедуру sp_addmessage.

Хранимая процедура sp_addmessage

Мы можем добавить пользовательское сообщение, выполнив хранимую процедуру sp_addmessages. Синтаксис процедуры:

EXEC Sp_addmessage 

  @msgnum= 70001, 

  @severity=16, 

  @msgtext='Please enter the numeric value', 

  @lang=NULL, 

  @with_log='TRUE', 

  @replace='Replace'; 

@msgnum: задает номер сообщения. Тип данных параметра — integer. Это ID пользовательского сообщения.
@severity: указывает уровень серьезности ошибки. Допустимые значения от 1 до 25. Тип данных параметра — smallint.
@messagetext: задает текст сообщения, который вы хотите выводить. Тип данных параметра nvarchar(255), значение по умолчанию NULL.
@lang: задает язык, который вы хотите использовать для вывода сообщения об ошибке. Значение по умолчанию NULL.
@with_log: этот параметр используется для записи сообщения в просмотрщик событий. Допустимые значения TRUE и FALSE. Если вы задаете TRUE, сообщение об ошибке будет записано в просмотрщик событий Windows. Если выбрать FALSE, ошибка не будет записана в журнал ошибок Windows.
@replace: если вы хотите заменить существующее сообщение об ошибке на пользовательское сообщение и уровень серьезности, вы можете указать это в хранимой процедуре.

Предположим, что вы хотите создать сообщение об ошибке, которое возвращает ошибку о недопустимом качестве (invalid quality). В операторе INSERT значение invalid_quality находится в диапазоне между 20 и 100. Сообщение следует рассматривать как ошибку с уровнем серьезности 16.

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

USE master;

go 

EXEC Sp_addmessage 

  70001, 

  16, 

  N'Product Quantity must be between 20 and 100.'; 

go

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

USE master 

go 

SELECT * FROM   sys.messages WHERE  message_id = 70001 

Вывод:

Как использовать пользовательские сообщения об ошибках

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

Мы создали сообщение с ID = 70001. Оператор RAISERROR должен быть таким:

USE master 

go 

RAISERROR (70001,16,1 ); 

go

Вывод:

Оператор RAISERROR вернул пользовательское сообщение.

Хранимая процедура sp_dropmessage

Хранимая процедура sp_dropmessage используется для удаления пользовательских сообщений. Синтаксис оператора:

EXEC Sp_dropmessage @msgnum

Здесь @msgnum задает ID сообщения, которое вы хотите удалить.

Теперь мы хотим удалить сообщение, с ID = 70001. Запрос:

EXEC Sp_dropmessage 70001

Выполним следующий запрос для просмотра сообщения после его удаления:

USE master 

go 

SELECT * FROM   sys.messages WHERE  message_id = 70001 

Вывод:

Как видно, сообщение было удалено.

Transact-SQL provides facilities to raise errors and warnings, as well as handling them. Here’s an introduction to this subject.

Raising errors and warnings

In SQL Server, we can generate an error or a warning with the RAISERROR statement. The most common usage looks like this:

RAISERROR (
    'Invalid user id',
    20, -- severity
    0   -- state
);

In this example, we’re raising a fatal error with severity 25 and state 0.

Severity must be in the range between 0 and 19. 0-18 represents a warning, 19-25 represents an error.

State must be in the range between 0 and 255. has no effect on SQL Server itself, but it’s useful for handling the error programmatically. If an error with the same message and severity can be raised in several places, the state can be used to find out where the error was raised.

Severity and state may be adjusted by SQL Server. If they’re less then 0 the value will be 0. If they exceed the maximum value, the maximum value will be used.

An error is a fatal error that terminates the current operation, and a warning is an anomaly that could indicate a problem or not, depending on the situation. Let’s see the differences, and how to generate them.

Errors

A mentioned, an error has a severity from 19 to 25.

An error must be written into the logs, so the WITH LOG option is required, as follows:

RAISERROR (...) WITH LOG;

An error is considered fatal, so it has some effects:

• It rolls back the current transaction;
• It terminates the execution of the current T-SQL program (a trigger, a procedure, etc).

Warnings

As mentioned, a warning must have a severity from 0 to 18. Warnings can only be handled by a TRY ... CATCH statement if their severity is bigger than 10.

The WITH LOG option discussed above is optional for warnings.

A warning does not rollback the transaction and does not terminate a program execution. We can, however, do one of these things or both:

ROLLBACK;
RAISERROR (...);
RETURN 1;

The order of the statements is important here:

• ROLLBACK must precede RAISERROR, because if the warning is handled by a TRY ... CATCH block, anything after RAISERROR won’t be executed.
• RETURN should follow both ROLLBACK and RAISERROR, or they won’t be executed.

RETURN always returns an integer value, 0 by default. This value can be used to indicate if the procedure succeeded or not, and why it failed. Conventionally, 0 indicates success and any other value indicates failure. We can use different non-zero returned values to indicate different problems: for example 1 for wrong arguments, 2 for missing rows, etc.

Other options

RAISERROR has a few more options that can occasionally be useful.

NOWAIT

Errors are normally sent to the client (and shown to the user) when the current program or T-SQL statement ends. To send the error to the client immediately, specify WITH NOWAIT:

RAISERROR (...) WITH NOWAIT;

SETERROR

You can also use SETERROR to set the error number to 50000, regardless of the severity level. 50000 is the code reserver to general user defined errors, by convention.

Reference

More articles:

• SQL Server: Handle errors