你的位置:首页 > 数据库

[数据库]MSSql异常处理框架


模拟实现异常堆栈,报告调用链和异常位置,代码如下:

CREATE PROC [MyHelper].[ThrowError]  @ProcId INTAS--对于重新封装Error,微软有个raiserror方案:[sys].[sp_replrethrow]IF ERROR_NUMBER() IS NULL RETURN;DECLARE @_ProcName SYSNAME=ISNULL(LTRIM(RTRIM(OBJECT_NAME(@ProcId))),'出错位置不详')	,@_Num INT=ERROR_NUMBER()	,@_Msg NVARCHAR(4000)=ERROR_MESSAGE()	,@_Line NVARCHAR(100)=ISNULL(CONVERT(NVARCHAR,ERROR_LINE()),'未知')	,@_State TINYINT=ERROR_STATE()	,@_NumCaption NVARCHAR(50)	,@_MsgCaption NVARCHAR(50)	,@_Severity INT=ERROR_SEVERITY()	;IF @_Num<50000 BEGIN	SET @_NumCaption=' ,ErrorNum:'+CONVERT(NVARCHAR,@_Num);	SET @_Num=100000+@_Num;	SET @_MsgCaption=',Message:';	ENDELSE BEGIN	SET @_NumCaption='';	SET @_State=@_State+1;	SET @_MsgCaption=';'+CHAR(13)+char(10);	ENDSET @_Msg='第'+CONVERT(NVARCHAR,@_State)+'层:'	+'Procedure:'+@_ProcName+',Line:'+@_Line	+@_NumCaption	+ @_MsgCaption	+@_Msg;THROW @_Num,@_Msg,@_State;

使用方法:

BEGIN TRY	EXEC SP_EXECUTESQL N'SELECT 1 FROM tbl;';END TRYBEGIN CATCHEXEC MyHelper.ThrowError @@PROCID;END CATCHSET NOCOUNT OFF;