Customizing error messages in the THROW statement

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

Parameter placeholders in RAISERROR and THROW

RAISERROR('No %s with id %d.', 16, 1, 'staff member', 15);

THROW 52000, 'No staff member with id 15', 1;

Ways of customizing error messages

Variable by concatenating strings
FORMATMESSAGE function

Using a variable and the CONCAT function

DECLARE @staff_id AS INT = 500;
DECLARE @my_message NVARCHAR(500) =
    CONCAT('There is no staff member for id ', @staff_id, '. Try with another one.');

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)
    THROW 50000, @my_message, 1;

Msg. 50000, Level 16, State 1, Line 5
There is no staff member for id 500. Try with another one.

The FORMATMESSAGE function

FORMATMESSAGE ( { ' msg_string ' | msg_number } , 
                [ param_value [ ,...n ] ] )

FORMATMESSAGE with message string

DECLARE @staff_id AS INT = 500;
DECLARE @my_message NVARCHAR(500) =
    FORMATMESSAGE('There is no staff member for id %d. %s ', @staff_id, 'Try with another one.');

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)
    THROW 50000, @my_message, 1;

Msg. 50000, Level 16, State 1, Line 6
There is no staff member for id 500. Try with another one.

FORMATMESSAGE with message number

SELECT * FROM sys.messages

| message_id | language_id | severity | is_event_logged | text                                                          |
|------------|-------------|----------|-----------------|---------------------------------------------------------------|
| 60000      | 1033        | 16       | 0               | This is a test message with one numeric parameter (%d)...     |
| 21         | 1033        | 20       | 0               | Warning: Fatal error %d occurred at %S_DATE. Note the error...|
| 101        | 1033        | 15       | 0               | Query not allowed in Waitfor.                                 |
| 102        | 1033        | 15       | 0               | Incorrect syntax near '%.*ls'.                                |
| 103        | 1033        | 15       | 0               | The %S_MSG that starts with '%.*ls' is too long...            |
| ...        | ...         | ...      | ...             | ...                                                           |

FORMATMESSAGE with message number

sp_addmessage 
     msg_id , severity , msgtext,
     [ language ],
     [ with_log { 'TRUE' | 'FALSE' } ],
     [ replace ]

msg_id > 50000

FORMATMESSAGE with message number

exec sp_addmessage 
    @msgnum = 55000, @severity = 16, @msgtext = 'There is no staff member for id %d. %s', @lang = N'us_english';

| message_id | language_id | severity | is_event_logged | text                                   |
|------------|-------------|----------|-----------------|----------------------------------------|
| 55000      | 1033        | 16       | 0               | There is no staff member for id %d. %s |

DECLARE @staff_id AS INT = 500;
DECLARE @my_message NVARCHAR(500) = FORMATMESSAGE(55000, @staff_id, 'Try with another one.');

IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)
    THROW 50000, @my_message, 1;

Msg. 50000, Level 16, State 1, Line 6
There is no staff member for id 500. Try with another one.

Let's practice!

Transactions and Error Handling in SQL Server