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

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

error

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.

List of Errors and severity level in SQL Server with catalog view sysmessages

List of Errors and severity level in SQL Server with catalog view sysmessages

Few days back I have written “Error handling with “THROW” command in SQL Server 2012”. After reading this article, one of the regular blog readers has asked me question why I have used 16 severities for the error?

This is really interesting question so I thought to answer him in the form of this blog post so that everyone who is unaware with error messages in SQL Server can be benefited.

16 is a default severity level and used for most user exception and that was the reason I have used it. You can get a list of error messages along with its severity level and error number from following catalog view.

[sourcecode language=”sql”]

SELECT *

FROM master.dbo.sysmessages

WHERE

error=8134 — error message number I got in previous article

AND msglangid = 1033; –language selection, 1033 represents US english

[/sourcecode]

 

There are total number of 10542 error message comes by default for language id 1033 (US English). Error messages given in total 22 language so 10542 error * repeated 22 times = 231924 rows in sysmessages.

Generally we have sixteen different severity level in SQL Server 2012 and each severity represents criticalness of an error. You can get a list of severity from the following TSQL.

[sourcecode language=”sql”]

SELECT DISTINCT severity

FROM master.dbo.sysmessages;

[/sourcecode]

Here is the brief description of different severity.

Severity level 0-10: These are just information message not actual error.

Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 in previous article and hence we got severity error 16.

Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.

Severity Level 18: This error represents nonfatal internal software error.

Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.

Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.

Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.

Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.

Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.

Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.

Older version of SQL Server had Severity Level 25 as well but it is unexpected system error and doesn’t list in SQL Server 2012’s sysmessages catalog view.

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.