RAISERROR

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

Raise errors statements

RAISERROR
THROW
Microsoft suggests THROW

RAISERROR syntax

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

RAISERROR with message string

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 with message string

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

Other characters: %i, %o, %x, %X, %u...

RAISERROR with error number

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 - Example with 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 - Example with 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 |

Let's practice!

Transactions and Error Handling in SQL Server