sql server 2005中新增加的try catch，可以很容易捕捉異常了，今天大概學(xué)習(xí)看了下，歸納下要點如下

基本用法begin try
     {  sql_statement |
 statement_block  }
end try
begin catch
     {  sql_statement |
 statement_block }
end catch
，和普通語言的異常處理用法差不多，但要注意的是，sql server只捕捉那些不是嚴(yán)重的異常，當(dāng)比如數(shù)據(jù)庫不能連接等這類異常時，是不能捕捉的一個例子：begin try
  declare @x int
  -- divide by zero to generate error
  set @x = 1/0
  print 'command after error in try block'
end try
begin catch
  print 'error detected'
end catch
print 'command after try/catch blocks' 
另外try catch可以嵌套begin try
  delete from grandparent where name = 'john smith'
  print 'grandparent deleted successfully'
end try
begin catch
   print 'error deleting grandparent record'
   begin try
     delete from parent where grandparentid =
     (select distinct id from grandparent where name = 'john smith')
     print 'parent deleted successfully'
   end try
   begin catch
     print 'error deleting parent'
     begin try
       delete from child where parentid =
     (select distinct id from parent where grandparentid =
     (select distinct id from grandparent where name = 'john smith'))
       print 'child deleted successfully'
     end try
     begin catch
       print 'error deleting child'
     end catch
   end catch
 end catch
另外，sql server 2005在異常機制中，提供了error類的方法方便調(diào)試，現(xiàn)摘抄如下，比較簡單，不予以解釋error_number(): returns a number associated with the error.error_severity(): returns the severity of the error.error_state(): returns the error state number associated with the error.error_procedure(): returns the name of the stored procedure or trigger in which the error occurred.error_line(): returns the line number inside the failing routine that caused the error. error_message(): returns the complete text of the error message. the text includes the values supplied for any substitutable parameters, such as lengths, object names, or times. 最后舉例子如下，使用了error類的方法begin try
  declare @x int
  -- divide by zero to generate error
  set @x = 1/0
  print 'command after error in try block'
end try
begin catch
  print 'error detected'
  select error_number() ernumber,
         error_severity() error_severity,
         error_state() error_state,
         error_procedure() error_procedure,
         error_line() error_line,
         error_message() error_message
end catch
print 'command after try/catch blocks'
最后輸出error detected
err_num err_sev err_state err_proc             err_line  err_msg
------- ------- --------- -------------------- --------- --------------------------------
8134        16          1 null                 4        divide by zero error encountered.