如果使用try / catch块,则严重性为11-19的raiserror错误编号将导致执行跳转到catch块。
任何严重性超过16是系统错误。 为了演示下面的代码设置一个try / catch块并执行一个我们认为会失败的存储过程:
假设我们有一个表[dbo]。[Errors]来保存错误假设我们有一个存储过程[dbo]。[AssumeThisFails]当我们执行时会失败
-- first lets build a temporary table to hold errors if (object_id('tempdb..#RAISERRORS') is null) create table #RAISERRORS (ErrorNumber int, ErrorMessage varchar(400), ErrorSeverity int, ErrorState int, ErrorLine int, ErrorProcedure varchar(128)); -- this will determine if the transaction level of the query to programatically determine if we need to begin a new transaction or create a save point to rollback to declare @tc as int; set @tc = @@trancount; if (@tc = 0) begin transaction; else save transaction myTransaction; -- the code in the try block will be executed begin try declare @return_value = '0'; set @return_value = '0'; declare @ErrorNumber as int, @ErrorMessage as varchar(400), @ErrorSeverity as int, @ErrorState as int, @ErrorLine as int, @ErrorProcedure as varchar(128); -- assume that this procedure fails... exec @return_value = [dbo].[AssumeThisFails] if (@return_value <> 0) raiserror('This is my error message', 17, 1); -- the error severity of 17 will be considered a system error execution of this query will skip the following statements and resume at the begin catch block if (@tc = 0) commit transaction; return(0); end try -- the code in the catch block will be executed on raiserror("message", 17, 1) begin catch select @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ERROR_PROCEDURE(); insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure) values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure); -- if i started the transaction if (@tc = 0) begin if (XACT_STATE() <> 0) begin select * from #RAISERRORS; rollback transaction; insert into [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure) select * from #RAISERRORS; insert [dbo].[Errors] (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure) values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure); return(1); end end -- if i didn't start the transaction if (XACT_STATE() = 1) begin rollback transaction myTransaction; if (object_id('tempdb..#RAISERRORS') is not null) insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure) values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure); else raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); return(2); end else if (XACT_STATE() = -1) begin rollback transaction; if (object_id('tempdb..#RAISERRORS') is not null) insert #RAISERRORS (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure) values (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProcedure); else raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); return(3); end end catch end