Exception Handling Mechanism (begin try begin catch)

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