關於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