I'm using a stored procedure to insert into a table, and first checking to be sure the data is valid. If it isn't, I'm using throw to throw an error:
;THROW 50000, @Msg, 1;
The specifications say the error number must be between 50000 and 2147483647 (inclusive). Is there some reason I shouldn't just use 50000 for all of errors I throw?
My code would generally consider any SQL errors to be integration errors, or I would check the text of the error message. Is there some best practice for numbering I can't see?
If it's relevant, my code is in VB.NET, but I imagine it should apply to any program.
I'm using a stored procedure to insert into a table, and first checking to be sure the data is valid. If it isn't, I'm using throw to throw an error
The first thing to consider is that not every instance of throwing an error is because of invalid data. In a stored procedure, there are many scenarios where you may decide to throw an error explicitly.
The primary point of associating number codes with errors is for categorizing errors. Consider the following potential error categories:
50000 - Invalid data
50001 - Stored Procedure Already Executed Today (for daily jobs)
50002 - Timeout
50003 - Referential Integrity Conflict
...
Just because you may only be throwing an error for a specific error category doesn't mean SQL servers should only support a single category of errors. Developers are free to come up with as many error categories as they wish (within the constraints of 50000-2147483647).
Also worth noting is that @Msg may not be enough information in some cases. The code you've provided is generic so we don't know what @Msg is, but it could have specifics beyond the error category, such as "What about the data was invalid for inserting into this table?" Because of this, the @Msg may be different for various errors thrown within the same stored procedure. That makes it very difficult to go through error logs and do things such as aggregate the most commonly occurring errors.
You are free to use whatever error number convention you wish within your application. However, there are many reasons why developers may choose to not use 50000 for all of the errors they throw. One of the main considerations for using different error codes is to classify errors by category. From there, anything from driving additional business logic or aggregating error logs and more can be performed.