Getting an SQL Exception about being unable to convert one data type to another, but only when a field is too long?

When I get this error, it is because I am using + as a string concatenation operator, but one (or more) of the arguments is an integer. By the rules that SQL Server uses, if any argument is an integer, then the + is treated as addition rather than string concatenation.

Unfortunately, with a 10,000-line stored procedure you have few options (there could be a diatribe here about software engineering, making code more modular, and using constraints to validate data, but that would not be helpful).

What can you do? I don't actually know. You have a bug in your code, in the sense that the stored procedure is not expecting the types of values you are providing (and all the more ironic because it sounds like most of the procedure is validating values). Some logic in the stored procedure is changing the shorter value to something acceptable for numeric conversion, perhaps something like substring(val, 2, 5) and expecting the value to be an integer.

You could prevent the error by using the concat() function for string concatenation in the stored procedure rather than +. However, that might just hide some other error in the code. You could also prevent the error by pre-validating the data and preventing certain rows from being passed in.