SQL SERVER - What is RAISERROR with details and Example

Using RAISERROR, we can throw our own error message while running our Query or Stored procedure.

·        It allows developers to generate their own messages

·        It returns the same message format that is generated by SQL Server Database Engine

·        We can set our own level of Severity for messages

·        It can be associated with Query and stored procedure

·        ERROR message can have 2047 character and show only 2044

-- syntax 

RAISERROR ( { Message ID | Message Text} { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

·        Custom error message ID should be greater than 5000.

·        Severity option should between 0 to 25 and for fatal error 20 to 25

·        State option is default set 1 but we can set 1 to 127

·        With option can set for log it can true or false like as:-

 Example:

exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='my custom error message text',@with_log='true'

We can see error massage in sys.messages view and using sp_addmessage procedure we can add new custom error message in sys.messages view.

exec sp_addmessage @msgnum=50009,@severity=1,@msgtext='Adding Custom Error Message'

OR

 

            SELECT * FROM sys.messages