MSSql异常处理框架

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

CREATE PROC [MyHelper].[ThrowError]
    @ProcId INT
AS
--对于重新封装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：';
	END
ELSE BEGIN
	SET @_NumCaption='';
	SET @_State=@_State+1;
	SET @_MsgCaption='；'+CHAR(13)+char(10);
	END

SET @_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 TRY
BEGIN CATCH
EXEC MyHelper.ThrowError @@PROCID;
END CATCH
SET NOCOUNT OFF;

 

转载于:https://www.cnblogs.com/microtry/p/5755294.html