Distinguish errors I raise from other SQL Server errors

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment