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.’;
GO

–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′;
[/sourcecode]

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.
BEGIN TRY
DECLARE @Name VARCHAR(50),@City AS VARCHAR,@Count INT
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
BEGIN
BEGIN TRANSACTION
INSERT INTO TestCustomError (Name,City)
SELECT @Name,@City
COMMIT TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMessage varchar(500) = FORMATMESSAGE(50001, @Name, ‘TestCustomError’);
THROW 50001, @ErrMessage, 1;
END
END TRY

BEGIN CATCH
THROW;
END CATCH
GO
[/sourcecode]

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

http://Extreme-Advice.com

http://www.sqlhub.com

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