Exception Handling Mechanism (begin try begin catch)

Source: Internet
Author: User
Tags error status code
Begin Try
-- SQL
End Try
Begin Catch -- SQL (handling error Actions)
End Catch

We will write the SQL statements that may cause errors in begin try... End If an error occurs between try and try Program Jump to the next begin try... End Try beign catch... End Catch

, Execute beign catch... End Catch error handling SQL. Try .. catch can be nested.

In begin catch... End In catch, we can use the following four functions provided by the system to get error information:

Error returned by error_number Code

Error severity returned by error_serverity

Error status code returned by error_state

Error_message returns the complete error message.

The above four functions are in the same begin catch... End Catch can be used multiple times, and the value remains unchanged.

The following is a simple example.

Begin Try
Select 2 / 0
End Try

Begin Catch
Select Error_number () As Error_number,
Error_message () As Error_message,
Error_state ()As Error_state,
Error_severity () As Error_severity
End Catch


Result:

-- ---
Error_number error_message error_state error_severity

8134 An error occurs when the Division is zero. 1 16

-- -----------------------------------------------------

Not try... Catch structure impact Error
Try... Catch constructor does not catch errors in the following situations:

Severity Level: 10 Or lower warning or information messages.

Severity Level: 20 Or higher than the SQL Server database engine task that terminates the session. If the severity of the error is 20 Or higher, while the database connection is not interrupted, try... Catch will handle this error.

Messages to be concerned, such as client interruption requests or client connection interruption.

Used by the system administrator Kill Statement to terminate a session.



Use Adventureworks;
Go

Begin Try
-- Generate a divide-by-zero error.
Select 1 / 0 ;
End Try
Begin Catch
Select
Error_number () As Errornumber,
Error_severity () As Errorseverity,
Error_state () As Errorstate,
Error_procedure () As Errorprocedure,
Error_line () As Errorline,
Error_message () As Errormessage;
End Catch;
Go



Use Adventureworks;
Go
Begin Transaction ;

Begin Try
-- Generate a constraint violation error.
Delete From Production. Product
Where Productid = 980 ;
End Try
Begin Catch
Select
Error_number () As Errornumber,
Error_severity () As Errorseverity,
Error_state () As Errorstate,
Error_procedure () As Errorprocedure,
Error_line () As Errorline,
Error_message () As Errormessage;

If @ Trancount > 0
Rollback Transaction ;
End Catch;

If @ Trancount > 0
Commit Transaction ;
Go

From: http://blog.csdn.net/htl258/article/details/4125446

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.