Friends,
We creates many stored procedures when we are working on database applications. While working on stored procedures(SPs) , we frequently need to raise custom errors and RAISERROR is the best option available to raise custom error messages.
We can raise errors in two ways –
- Adhoc
- Add error message to sys.messages table and refer it in SPs.
Adhoc – In this way we can raise the error adhoc by providing the error message. PFB a simple example to show the same.
RAISERROR(‘This is a test custom error message’,0,1)
Adding to Sys.messages table – In this approach we add the custom error message to sys.messages table of Master database. The custom message number should be more than 50000 for user defined messages. PFB the sample code –
Use Master
Go
sp_addmessage @msgnum = 50001,@severity =1,@msgtext = ‘I am testing this’,@lang = ‘us_english’
RAISERROR(50001,0,1)
In the above sample code, I selected the Master database , then added error message to sys.messages table using stopred procedure sp_addmessage and using RAISERROR we are refering to the added error using message number.
If you ask me which approach is better then I will say both has its own advantages. When the custom error needs to be raised in just one place then go for the first approach. The advantages of second approach are
- Will be added once and can be referred in many places.
- Making changes in all the places can be easily done by changing in sys.messages table
- For same message id we can have different messages for different languages. It is of great use when application is having multi language user interfaces.
So guys, who are not aware of the using custom messages, start using it now. It is very best and easy way of raising custom messages.
Regards,
Roopesh Babu V
