sql server 2005中新增加的try catch学习

sql server 2005中新增加的try catch，可以很容易捕捉异常了，今天大概学习看了下，归纳下要点如下

基本用法begin try
     {  sql_statement |
 statement_block  }
end try
begin catch
     {  sql_statement |
 statement_block }
end catch
，和普通语言的异常处理用法差不多，但要注意的是，sql server只捕捉那些不是严重的异常，当比如数据库不能连接等这类异常时，是不能捕捉的一个例子：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类的方法方便调试，现摘抄如下，比较简单，不予以解释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.