Exception handling in T-SQL Programming-exception capture (try catch) and throw exception (throw)

Original: Exception handling in T-SQL Programming-exception capture (try catch) and throw exception (throw)

The source of this article: http://www.cnblogs.com/wy123/p/6743515.html

T-SQL programming, like applications, has exception handling mechanisms, such as exception catching and exception throwing (try Catch throw), and this article simply describes the actual use of exception catching and exception throwing in T-SQL programming.

Simple description of exception handling

Exception trapping is very common in application programming and provides methods for any unexpected or unusual conditions that occur when a handler runs
Just graduated from the abnormal handling confusion, especially catch and throw, since catch or not catch, will throw, why should catch after throw? What exactly do you want to do with the catch?
Later, the more contact began to slowly understand the mechanism of exception handling, in the application and T-SQL should be similar
It is easy to understand this:
For the UI level, exception capture, the personal understanding is to capture the code snippet that might have occurred, giving user-friendly hints,
A way to prevent an application from crashing (or throwing a page to a user with a background code error).
If this is the underlying method (the bottom layer can understand that a method calls the B method, the B method calls the C method, the C method is the underlying method),
Exception handling can continue to be thrown to the upper-level caller after capture, letting the caller know what is wrong with the method it is calling.
For the code that has the exception, log the reason for the exception so that it can be used to troubleshoot the problem.
For example, an exception occurred in the C method, to tell the "B" method called "I have an exception, the exception is * * *", this is a C will throw an exception,
At the same time, C to record the abnormal information (by different means to record the above abnormal reasons), for subsequent troubleshooting problems for reference.

The above is the theoretical basis, the following in T-SQL exception processing as an example, a brief introduction of the exception handling and what to do, T-SQL exception handling.

Handling exception information in Catch blocks

Let's start with the following two tables to illustrate the example. A product information sheet with Product ID and price information

　　

The following is a stored procedure that shows how to handle exception handling with a new product information
The following is a stored procedure for adding product information, which is inserted into the product table according to the parameters,

　　

In the process of inserting the data, an exception was caught, in the catch code, there are two operations,
The first step is to insert the exception information into Exceptionlog, of course, the format of this exception information can be defined by itself, the second step throws an exception (throw), based on the above theory
The first reason to record the exception, this is easy to understand, a write the stored procedure to B to call, B called the time of the exception, the exception information recorded in favor of a to troubleshoot the specific cause of the exception
Then throw an exception to tell B that the execution of the stored procedure produced an exception and that your operation did not execute successfully.
For example, the following code, the execution of a primary key conflict exception, the role of throw is to tell the caller, the execution of the stored procedure when an exception occurred, and the detailed error information thrown

Of course, the actual common anomalies are also more, such as deadlock, the main foreign key conflict, execution timeout, no operational permissions, and so on a variety of unpredictable exceptions, including the format of recording exception information, can be freely selected.

　　

The Exceptionlog information recorded in the catch is observed at this time, and the following
The exception information that is logged is intended to be an afterthought, unlike the exception that is directly "thrown" above.
One is to tell the caller at the time of the exception, throwing the exception is indicating that the current execution of the code has an exception, Exceptionlog log exception information is done to troubleshoot the reason for the analysis of the use

If you do not throw an exception, such as the following code, comment out the throw statement, is equal to the catch block simply record the exception after the "Eat" exception, what happens

　　

For example, if a record of id = 1 already exists in the product table, executing the following code is a failure,
But the client does not have any hint, the caller does not know what happened, when the call to the stored procedure failed or succeeded? There is not a clear answer.
This is the consequence of eating exceptions in catch.
So normally, after the exception is logged in the catch, to "throw" the exception, when the exception occurs, explicitly tell the caller what happened.

　　

To explicitly throw an exception using throw

In some cases it is necessary to actively throw an exception to interrupt the execution of the logic, what does it mean?
That is, the current logic, only to meet certain conditions can be executed, if the condition is not satisfied, it is necessary to explicitly tell the caller that your condition is not satisfied, the current logic can not be executed properly.
For example, or a stored procedure that just inserted the product information, as follows
When inserting product information, only product price greater than 0 is a valid product information, otherwise cannot be inserted,
At this point, you can explicitly tell the caller by throwing an exception (there are other ways, of course), your arguments are illegal, throw a custom exception with throw, and force the execution of the code to break.

　　

The above method simply shows that the arguments passed by the caller are, normally, verified and that there are no too low-level errors.
Of course, the actual application should be more complex than this, there is no guarantee that the caller is from the user graphical interface (UI) initiated, that is, can not be directly pre-processing to ensure the legitimacy of the input information
For a stored procedure similar to the above
First, there is no guarantee that the arguments passed by the caller forever are valid, and second, even the caller cannot ensure that the logic of their generated arguments never fails.
At this point in the logically demanding program, you need to do something like the above to actively throw an exception to interrupt the execution of the code.
Of course, the above problems are handled in more ways than one, only the application scenarios and methods of exception handling.

　　

Use of the throw statement

Finally, say the throw statement,

　　

Throw is required raiserror more convenient and intuitive exception-throwing way, but also recommended exception handling methods, specific differences on the Internet a lot of said
Throw has two ways of using it, throwing a custom exception and throwing an exception directly in the CATCH block.
When a custom exception is thrown, there are three mandatory parameters, which are described below, where the catch block can be thrown directly with a throw without any arguments to throw the caught exception
The previous sentence of the throw statement requires a semicolon, and the preceding sentence is not guaranteed to have a semicolon,
Therefore, the semicolon can be written directly in front of the throw, such as the text; throw 50000, ' price can is less is less than 0 ', 1 notation
When you throw a custom error, the throw statement has three parameters, as follows
The throw statement security level defaults to 16 and is not modified, in other words, the throw statement throws an error after execution, interrupting the batch statement of the current session, and the statement following the throw will not execute
The first parameter is the error number, and the user custom error number is greater than 50000 (50000 to 2147483647)
The second parameter is a custom error message content that can be customized as needed
The third parameter is the error state, and his role is to mark where the exception occurred,
For example, the same "parameter cannot be less than 0" error, the entire stored procedure may have two places to do the same check
You can use the error state to throw an exception in two places, respectively;
Throw 50000, ' price can not is less than 0 ',1
Throw 50000, ' price can not is less than 0 ',2,
Because the error state is different, it is more convenient to know where the exception is due to the specific error state, referring to

The throw statement stores the custom exception to the Messages system table
Custom exception information can be added to the sys.messages system table, in English, plus Chinese.
You can then use this predefined message instead of writing one string at a time.

EXECsp_addmessage@msgnum = 50001,       @severity =  -,       @msgtext =N'%s can not is less than 0',       @lang = 'us_english'; EXECsp_addmessage@msgnum = 50001,       @severity =  -,       @msgtext =N'%s cannot be less than 0',       @lang = 'Simplified Chinese';

as follows, the stored procedure first formats the exception information through FormatMessage, then throws the exception using the throw 50001, @msg, 1;

Then, when the stored procedure is called, the following is the scenario in which a custom exception is thrown when the exception is triggered.

　　

　　

Summary: This paper briefly introduces the method of exception handling in T-SQL programming, which can be used to catch abnormal information and record it, ensure the maintenance of code, and provide reference for troubleshooting.
It is also possible to terminate the execution of the code in a manner that ensures that the business logic is satisfied by proactively throwing a custom exception to ensure the correctness of the business logic.

Exception handling in T-SQL Programming-exception capture (try catch) and throw exception (throw)