Distinguish errors I raise from other SQL Server errors

All we need is an easy explanation of the problem, so here it is.

In my stored procedures, when a business rule is broken I raise an error that bubbles up to the C# client app and gets displayed to the user. e.g.

RAISERROR('Hey, you cannot do that because blah blah blah', 16, 1);

I want to distinguish errors that I raise from other SQL Server errors, because I only want my errors to get displayed.

I think these are the only ways to send a message to the client app that this is a user message to display: Severity level, State, Return Code. But I think I should leave the severity level.

  • How to I tell my client app?
  • And what code or number should I use that is not already being used?
  • Or is there another way I have not considered?

Or should I do this:

THROW 50000, 'Hey, you cannot do that!', 1;  

EDIT (13-April-2022)
I asked Erland Sommarskog whose response helped me realise that if you use this

RAISERROR('Hey, you cannot do that because blah blah blah', 16, 1);

…then the client app will always get 50000 as the error number. And with RAISERROR you can use parameterized messages. You just need to be aware that RAISERROR will not exit the batch. So, for me, RAISERROR wins.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You should be using THROW anyway as RAISERROR is somewhat deprecated per Microsoft’s docs:

The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

Per the docs on THROW, the error_number parameter must be a number greater than or equal to 50000 and less than or equal to 2147483647. (The first 49,999 error codes are already reserved for native SQL Server generated errors.)

If you are using C# in your client app, then when your application code receives an exception from your SQL Server, that exception object will be castable to a SQLException object. (System.Data.SqlException object if you’re still using the old assembly reference.) This will expose all the properties of the custom exception you threw, and specifically the Number property will contain your custom error code.

Personally, I like to keep a collection of Enums for each user defined error code I’m utilizing in my SQL Server instance.

If you do choose to continue to use RAISERROR you can also look into creating and using your own user defined error messages with sp_addmessage as a way to refactor the actual message to a custom error code in SQL Server, so that you can reference the same object each time you throw the same exception. (I’ve personally never used this feature before though.) But I’d personally recommend sticking to THROW.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply