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:
--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';
Now, we have one custom message ready with us, let us check it by creating one sample table with some sample data row.
--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
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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.