SQL SERVER 里的 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
 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;
 

另外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
 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;
 

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.