RAISERROR

SQL Server에서의 트랜잭션과 오류 처리

Miriam Antona

Software Engineer

오류 발생 구문

RAISERROR
THROW
Microsoft는 THROW를 권장합니다

RAISERROR 구문

RAISERROR ( { msg_str | msg_id | @local_variable_message },  
    severity, 
    state,
    [ argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]

문자열 메시지로 RAISERROR

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = 15)
    RAISERROR('No staff member with such id.', 16, 1);

Msg. 50000, Level 16, State 1, Line 3
No staff member with such id.

문자열 메시지로 RAISERROR

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = 15)
    RAISERROR('No %s with id %d.', 16, 1, 'staff member', 15);

Msg. 50000, Level 16, State 1, Line 3
No staff member with id 15.

RAISERROR('%d%% discount', 16, 1, 50);

Msg. 50000, Level 16, State 1, Line 1
50% discount

기타 지정자: %i, %o, %x, %X, %u...

오류 번호로 RAISERROR

RAISERROR(60000, 16, 1);

Msg. 60000, Level 16, State 1, Line 1
This is a test message.

SELECT * FROM sys.messages

| message_id | language_id | severity | is_event_logged | text                      |
|------------|-------------|----------|-----------------|---------------------------|
| ...        | ...         | ...      | ...             | ...                       |
| 60000      | 1033        | 16       | 0               | This is a test message    |
| ...        | ...         | ...      | ...             | ...                       |

RAISERROR - TRY...CATCH 예시

BEGIN TRY
    IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = 15)
        RAISERROR('No staff member with such id.', 9, 1);
END TRY
BEGIN CATCH
    SELECT 'You are in the CATCH block' AS message
END CATCH

No staff member with such id.
Msg. 50000, Level 9, State 1

RAISERROR - TRY...CATCH 예시

BEGIN TRY
    IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = 15)
        RAISERROR('No staff member with such id.', 16, 1);
END TRY
BEGIN CATCH
    SELECT 'You are in the CATCH block' AS message
END CATCH

| message                    |
|----------------------------|
| You are in the CATCH block |

연습해 봅시다!

SQL Server에서의 트랜잭션과 오류 처리