Create custom error message by sys.sp_addmessage in SQL Server 2012

Create custom error message by sys.sp_addmessage in SQL Server 2012


Social media is a wonderful way to share knowledge. I have my own Facebook page where I used to share my knowledge. Yesterday I see one message in my Facebook inbox. One SQL Server community member asked me whether it is possible to create our own error messages in SQL Server or not. He told me that after reading two of my previous articles (Error handling with “THROW” command in SQL Server 2012 and List of Errors and severity level in SQL Server with catalog view sysmessages) related to error handling, he has looked at catalog view “Master.DBO.SysMessages” and he wanted to define his own custom error messages.

Microsoft have given control to us to create custom messages as per our need and system stored procedure “sys.sp_addmessage” helps us to do this.

Let us look at one example for this:

[sourcecode language=”sql”]
–I want to add error with error number 50001
–let us check whether any error is having same number or not
–in my case, I don’t find any error with 50001 number
select * from master.dbo.sysmessages where error =’50001′;

–adding error message with 50001 number and severity 16
–if record duplicate records comes, we can execute this error
EXEC sys.sp_addmessage 50001, 16, ‘%s is already there in table %s.’;

–now we will get one row for default language (in my case it is 1033 which is US English)
–with 50001 number
select * from master.dbo.sysmessages where error =’50001′;

Now, we have one custom message ready with us, let us check it by creating one sample table with some sample data row.

[sourcecode language=”sql”]

–now testing the error message whether it is working fine or not.
SET @Name=’Rajan Shah’
SET @City=’Mumbai’

SELECT @Count=COUNT(1) FROM TestCustomError WHERE Name=@Name

–In this case, I have considered that Name column should be unique
–there may or may not be Primary or Unique Key defined
–but we can test it in business logic
–there may be argument for this approach but I just wanted to show
–whether custom error is working or not.
IF @Count<=0
INSERT INTO TestCustomError (Name,City)
SELECT @Name,@City
DECLARE @ErrMessage varchar(500) = FORMATMESSAGE(50001, @Name, ‘TestCustomError’);
THROW 50001, @ErrMessage, 1;


Scope of this article was to show how to add custom error message and check it. I have kept one loophole by not putting ROLLBACK anywhere in the code which itself is an interesting topic and out of the scope of this article. I will cover this point very soon.

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Create custom error message by sys.sp_addmessage in SQL Server 2012”

Comments are closed.