因要记录存储过程中sql里面遇到的错误,以方便开发测试时追踪、改进。但存储过程有100+,每个都写一大堆相同的错误处理,痛苦死了。于是把错误处理弄了个存储过程:
处理错误记录的储存过程
USE
[
WebE
]
GO
/* ***** 对象: StoredProcedure [dbo].[ErrorHandle] 脚本日期: 08/09/2010 09:48:13 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [ dbo ] . [ ErrorHandle ]
@ErrorMessage varchar ( 1000 ) output
As
declare @ErrorProcedure varchar ( 1000 );
declare @ErrorNumber int ;
declare @ERRORLINE int ;
declare @ErrorSeverity int ;
declare @ErrorState int ;
set @ErrorProcedure = Error_Procedure();
set @ErrorNumber = error_number();
set @ERRORLINE = ERROR_LINE();
set @ErrorMessage = error_message()
-- +' 错误的存储过程:'+@ErrorProcedure +' 错误行号:'+cast( @ERRORLINE as varchar(30))+' 错误代号:'+ cast (@ErrorNumber as varchar(30));
set @ErrorSeverity = Error_Severity();
set @ErrorState = Error_State();
-- RaisError(@ErrorMessage,@ErrorSeverity,@ErrorState); --再抛出的话会变成抛出两次
EXECUTE P_SysErrorInfo_Insert ' DB SERVER ' , @ErrorMessage , @ErrorProcedure , OUTPUT; -- 记录错误
print ' 错误已经记录在数据库SysErrorInfo表中 '
if @ErrorMessage is null
set @ErrorMessage = ''
return @ErrorMessage
GO
/* ***** 对象: StoredProcedure [dbo].[ErrorHandle] 脚本日期: 08/09/2010 09:48:13 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [ dbo ] . [ ErrorHandle ]
@ErrorMessage varchar ( 1000 ) output
As
declare @ErrorProcedure varchar ( 1000 );
declare @ErrorNumber int ;
declare @ERRORLINE int ;
declare @ErrorSeverity int ;
declare @ErrorState int ;
set @ErrorProcedure = Error_Procedure();
set @ErrorNumber = error_number();
set @ERRORLINE = ERROR_LINE();
set @ErrorMessage = error_message()
-- +' 错误的存储过程:'+@ErrorProcedure +' 错误行号:'+cast( @ERRORLINE as varchar(30))+' 错误代号:'+ cast (@ErrorNumber as varchar(30));
set @ErrorSeverity = Error_Severity();
set @ErrorState = Error_State();
-- RaisError(@ErrorMessage,@ErrorSeverity,@ErrorState); --再抛出的话会变成抛出两次
EXECUTE P_SysErrorInfo_Insert ' DB SERVER ' , @ErrorMessage , @ErrorProcedure , OUTPUT; -- 记录错误
print ' 错误已经记录在数据库SysErrorInfo表中 '
if @ErrorMessage is null
set @ErrorMessage = ''
return @ErrorMessage
P_SysErrorInfo_Insert 是错误记录的存储过程。