0

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.

0

1 Answer 1

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.

5
  • 1
    Note that THROW cannot utilize user-defines messages added with sp_addmessage. We very rarely see applications using sp_addmessage in the first place, and especially since THROW was added in the product (in 2012). Apr 4, 2022 at 13:56
  • 1
    Thanks @J.D. I'll go with THROW
    – Craig
    Apr 4, 2022 at 16:08
  • @TiborKaraszi Good to know, probably explains why I never use it lol.
    – J.D.
    Apr 4, 2022 at 16:55
  • Erland Sommarskog is less keen on THROW "Given that ;THROW with parameters is less versatile than RAISERROR without any particular simplicity in syntax, there is little reason why you would use it."
    – Paul White
    Apr 5, 2022 at 9:20
  • 1
    Thanks, @paul-white. Now I know that THROW does not support parameter markers and THROW always aborts the batch. But I'm going to stick with it because "THROW 50000, 'Hey, you cannot do that!', 1; " is easier with THROW.
    – Craig
    Apr 5, 2022 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.