Error Handling 2

Raise Error

• Raise Errors Statements

  • RAISERROR
  • THROW
  • Microsoft suggests THROW

        RAISEERROR ( {msg_str | msg_id | @local_variable_message},
            severity,
            state,
            [arguement [,...m ]])
            [ WITH option [, ...n]]
  

• RAISEERROR with message string

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

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

        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 such 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 - 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
  

---

THROW

• THROW syntax

  • Recommended by Microsoft over the RAISERROR statment.

    Syntax: THROW [error_number, message, state][;]

• THROW - without parameters

        BEGIN TRY
            SELECT price/0 from orders;
        END TRY
        BEGIN CATCH
            THROW;
            SELECT 'This line is executed!' as message;
        END CATCH
  

• THROW - ambiguity

        BEGIN TRY
            SELECT price/0 from orders;
        END TRY
        BEGIN CATCH
            SELECT 'This  line is executed;
            THROW;
        END CATCH
  

• THROW - with parameters

        BEGIN TRY
            IF NOT EXISTS(SELECT * FROM staff WHERE staff_id = 15)
                THROW 51000, 'This is an example', 1;
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS message;
        END CATCH
  

---

Customizing error messages in the THROW statement

• Parameter placeholders in RAISERROR and THROW

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

• Ways of customizing error messages

  • Variable by concatenating string
  • 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 anotherone.');
  
        IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)
            THROW 50000, @my_message, 1;
  

• The FORMATMESSAGE function

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

        DECLARE @staff_id AS INT = 500;
        DECLARE @my_message NVARCHAR(500) = FROMATMESSAGE('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;