Monday, September 10, 2012

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


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'

            SELECT * FROM sys.messages

Like and Share to SQL Integrity Blog

1 comment:

Thank You !!!!