MSSQL 에러처리하는 여러가지 방법

BEGIN TRY
BEGIN TRAN
    /************************************************
    *    Procedure 환경설정
    ************************************************/
    SET XACT_ABORT ON;

    /************************************************
    *    사용자변수 선언 BEGIN
    ************************************************/
    -- Declare variables used in error checking.  
    DECLARE @ErrorVar INT;  
    DECLARE @RowCountVar INT;  

    /************************************************
    *	업무처리 BEGIN
    ************************************************/
    /* 1. TRY...CATCH */
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  

    /* 2. @@ERROR, @@ROWCOUNT */
    -- Execute the UPDATE statement.  
    UPDATE PurchaseOrderHeader   
    SET BusinessEntityID = @BusinessEntityID   
    WHERE PurchaseOrderID = @PurchaseOrderID;  
  
    -- Save the @@ERROR and @@ROWCOUNT values in local   
    -- variables before they are cleared.  
    SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
    IF @ErrorVar <> 0 or @RowCountVar = 0
    BEGIN
        GOTO ErrorHandler
    END

    /************************************************
    *	업무처리 END
    ************************************************/
    GOTO EndHandler

    /************************************************
    *	정상 작업종료 처리
    ************************************************/
    EndHandler:
        PRINT '정상 작업종료 처리'
        COMMIT
        RETURN 0

    /************************************************
    *	에러 작업종료 처리
    ************************************************/
    ErrorHandler:
        IF @@TRANCOUNT <> 0
            ROLLBACK
        PRINT '에러 작업종료 처리'
        RETURN -1

END TRY

/************************************************
*	Exception 처리
************************************************/
BEGIN CATCH
    IF (XACT_STATE()) = -1
        ROLLBACK
    ELSE IF (XACT_STATE()) = 1
        ROLLBACK
    
    SELECT ERROR_NUMBER()    AS ErrorNumber    // 오류 번호를 반환
         , ERROR_SEVERITY()  AS ErrorSeverity  // 오류 심각도를 반환
         , ERROR_STATE()     AS ErrorState     // 오류 상태 번호를 반환
         , ERROR_PROCEDURE() AS ErrorProcedure // 오류가 발생한 저장 프로시저 또는 트리거의 이름을 반환
         , ERROR_LINE()      AS ErrorLine      // 오류를 발생시킨 루틴 내의 줄 번호를 반환
         , ERROR_MESSAGE()   AS ErrorMessage;  // 오류 메시지의 전체 텍스트를 반환
END CATCH

1. TRY...CATCH

BEGIN TRY
    /* 처리구분 */
END TRY 
BEGIN CATCH
    /* 오류처리 */
END CATCH

CATCH 블록에서 ERROR_MESSAGE 사용. 결과 집합은 다음과 같습니다.

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorLine  ErrorMessage
----------- ------------- ----------- --------------- ---------- ----------------------------------
8134        16            1           NULL            4          Divide by zero error encountered.

 

2. @@ERROR, @@ROWCOUNT

@@ERROR를 @@ROWCOUNT와 함께 사용하여 UPDATE 문 작업의 유효성을 검사합니다. 

@@ERROR의 값을 확인하여 오류 표시가 있는지 검사하고 @@ROWCOUNT를 사용하여 업데이트가 테이블의 행에 제대로 적용되었는지 확인합니다.

 

3. BEGIN TRANSACTION

오류가 발생할 경우 BEGIN TRANSACTION 이후에 발생한 모든 데이터 수정 사항을 롤백하여 데이터를 이러한 일관성 있는 상태로 되돌릴 수 있습니다.

 

트랜잭션이 오류 없이 완료되고 COMMIT TRANSACTION이 실행되어 수정 사항이 데이터베이스에 영구히 반영되거나, 오류가 발생하여 ROLLBACK TRANSACTION 문이 모든 수정 사항을 지울 때까지 모든 트랜잭션은 지속됩니다.

 

트랜잭션이 장기간 처리 중이면 다른 사용자가 이러한 잠긴 리소스에 액세스할 수 없습니다.

 

4. SET XACT_ABORT ON

SET XACT_ABORT 옵션을 ON으로 설정하면 Transact-SQL 문에서 런타임 오류가 발생할 경우 Transact-SQL이 현재 트랜잭션을 자동으로 롤백할지 여부를 지정합니다.

 

SET XACT_ABORT 옵션을 OFF로 설정하면 일부 경우에 오류를 일으킨 Transact-SQL 문만 롤백되고 처리 작업을 계속합니다. SET XACT_ABORT 옵션을 OFF로 설정한 경우에도 오류 심각도에 따라 전체 트랜잭션이 롤백될 수도 있습니다. T-SQL 문의 기본 설정은 OFF

 

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);

SET XACT_ABORT OFF;
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;

SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;

다음 코드 예제에서는 다른 Transact-SQL 문이 있는 트랜잭션에서 외래 키 위반 오류를 일으킵니다. 첫 번째 문 집합에서는 오류가 생성되지만 다른 문이 성공적으로 처리되고 트랜잭션이 성공적으로 커밋됩니다. 두 번째 문 집합에서는 SET XACT_ABORT 옵션이 ON으로 설정됩니다. 이렇게 설정하면 문 오류로 인해 일괄 처리가 종료되고 트랜잭션이 롤백됩니다.

 

5. @@TRANCOUNT

BEGIN TRANSACTION 문은 @@TRANCOUNT를 1씩 늘립니다. ROLLBACK TRANSACTION은 @@TRANCOUNT를 0으로 줄입니다. COMMIT TRANSACTION는 @@TRANCOUNT를 1씩 줄입니다.

 

A. BEGIN 및 COMMIT 문의 영향

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The COMMIT statement will decrement the transaction count by 1.  
    COMMIT  
    PRINT @@TRANCOUNT  
COMMIT  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--1  
--0

B. BEGIN 및 ROLLBACK 문의 영향

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The ROLLBACK statement will clear the @@TRANCOUNT variable  
--  to 0 because all active transactions will be rolled back.  
ROLLBACK  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--0

 

6. XACT_STATE

현재 실행 중인 요청의 사용자 트랜잭션 상태를 보고하는 스칼라 함수입니다. XACT_STATE는 요청에 활성 사용자 트랜잭션이 있는지 여부 및 트랜잭션이 커밋될 수 있는지 여부를 나타냅니다.

• 1 : 현재 요청에 활성 사용자 트랜잭션이 있습니다. 해당 요청에서는 데이터를 쓰고 트랜잭션을 커밋하는 등 모든 동작을 수행할 수 있습니다.
• 0 : 현재 요청에 대한 활성 사용자 트랜잭션이 없습니다.
• -1 : 현재 요청에 활성 사용자 트랜잭션이 있지만 오류가 발생하여 트랜잭션이 커밋할 수 없는 트랜잭션으로 분류된 상태입니다. 

XACT_STATE와 @@TRANCOUNT 함수는 모두 현재 요청에 활성 사용자 트랜잭션이 있는지 여부를 검색하는 데 사용될 수 있습니다. @@TRANCOUNT를 사용하여 트랜잭션이 커밋되지 않은 트랜잭션으로 분류되었는지 여부를 확인할 수는 없습니다. 또한 XACT_STATE를 사용하여 중첩된 트랜잭션이 있는지 여부를 확인할 수 없습니다.

 

 

 

---

참고

TRY...CATCH(Transact-SQL) - SQL Server

@@ERROR(Transact-SQL) - SQL Server

ERROR_MESSAGE(Transact-SQL) - SQL Server

SET XACT_ABORT(Transact-SQL) - SQL Server

BEGIN TRANSACTION(Transact-SQL) - SQL Server

@@TRANCOUNT(Transact-SQL) - SQL Server

XACT_STATE(Transact-SQL) - SQL Server