Can I use different SQLSTATE codes for "unhandled user-defined exception" other than "45000"?

Question

The question is about SIGNAL, like this:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Test error';

All the documentation and examples that I found only tells me that I can use the SQLSTATE "45000" to signal an "unhandled user-defined exception".

But can I use all the "45*" class for it?

For example can I signal "45001" for X situation, and "45002" for Y situation. It will help me to use conditions in the client application error handling.

I tested it and it works, seems that every "45*" is returning the error code 1644 and I can read the SQLSTATE from the exception and do my conditions.

But I cannot find any information that confirms it. Is it correct to do this? Is it permitted in MySQL?

If so, how much codes do I permitted to use? until 45999?

Answer 1

You can actually use whatever SQLSTATE you want in your application, with some conditions. The SQL standard specified 45--- as a dedicated subclass for user-thrown errors, although the standard doesn't prohibit from using other SQLSTATEs in the application code, as long as you follow the following:

1. The SQLSTATE you throw does have to be a valid (well-formed) SQLSTATE - that means, exactly five bytes with two first specifying a subclass (alphanumeric characters) and three next specifying the code within that subclass (only numbers). 45000, 45001, 39450, 11037, AA439, FE041 are all valid SQLSTATEs (although not all of them counts as errors - subclasses beginning with 0 are success states, those beginning with 1 are non-breaking warnings, beginning with 2 are "X not found" conditions, while any first-byte value larger than 2 classifies as an error and terminates execution unless caught by a handler). On the other hand, REEEE, G3,,,, DE0-1 are invalid SQLSTATEs, neither is i.e. 4500 (too short).
2. Avoid using SQLSTATEs already defined for something in the SQL standard specification outside of their original meaning - that could cause confusion during debugging. Keep a safe margin between your codes and spec-defined ones - the spec may potentially be updated by new SQLSTATEs defined in particular classes, and if no margin is kept, you'll be using spec-defined codes outside of their meaning.

A valid use case is to validate parameters or do in-procedure access control and throw exceptions on failures, when it's not enough to just return a well-known value and handle the situation in application code. In this case, you can even make a library of used SQLSTATEs, assign them to error messages in your application in a translatable resource, since the DB side will always return specified codes regardless of your users' locale.