关于SQLServer2005的学习笔记—异常捕获及处理

DECLARE @ErrNum INT,@RowCount INT;

-- 能够捕获的 0 做除数错误

SELECT 1/0;

SET @ErrNum=@@error;

SET @RowCount=@@rowcount;

PRINT ‘Encount ERROR‘;

PRINT @ErrNum

PRINT @RowCount

-- 不能被捕获的数据转换错误

SELECT ‘A‘+1

SET @ErrNum=@@error;

SET @RowCount=@@rowcount;

PRINT ‘Encount Serious ERROR‘;

PRINT @ErrNum

PRINT @RowCount

CREATE TABLE LogTable

(

         ID             INT IDENTITY (1,1) NOT NULL,

         ErrorNumber    INT,

         ErrorMessage   VARCHAR(1000),

         ErrorSeverity  INT,

         ErrorState     INT,

         ErrorLine      INT,

         ErrorProcedure VARCHAR(128)

);

CREATE PROCEDURE TestTryCatchProc

AS

-- 相关业务逻辑

BEGIN TRY

SELECT 1/0;

END TRY

-- 相关错误捕获

BEGIN CATCH

    PRINT ‘Error Numberv‘ + CAST(ERROR_NUMBER() AS VARCHAR(10));

    PRINT ‘Error Serverity: ‘ + CAST(ERROR_SEVERITY() AS VARCHAR(10));

    PRINT ‘Error State: ‘ + CAST(ERROR_STATE() AS VARCHAR(10));

    PRINT ‘Error Procedure: ‘ + ERROR_PROCEDURE();

    PRINT ‘Error Line: ‘ + CAST(ERROR_LINE() AS VARCHAR(10));

    PRINT ‘Error Message: ‘ + ERROR_MESSAGE();

         INSERT INTO LogTable(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)

        VALUES(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE())

    -- 此处可以先对相关错误进行 修正

END CATCH;

-- 执行相关存储过程，并查询 日志表，同时与 系统消息表进行对比

EXEC TestTryCatchProc

SELECT * FROM LogTable

SELECT * FROM sys.messages WHERE message_id=8134 AND language_id=2052