0

I have a massive stored procedure that I did not write. It's about 10K lines long. Part of it creates a few temp tables, inserts some records into these tables, then goes through about 8,000 lines of validation and removes invalid records from the tables and re-inserts them into a temp Error table for reporting back to the user.

Somewhere along the lines, I get the following SqlException in my C# code:

Conversion failed when converting the varchar value 'AAAAAAAABA683' to data type int.

But if I shorten the value down to just A683 or something, the procedure runs as expected and just marks this particular record as an error. So it sounds to me like it's not actually a problem with the data types, but something else.

Unfortunately the LineNumber property of the SqlException I catch doesn't help me, since I don't have access to seeing the actual SQL being executed. I can only look at the procedure itself.

Has anyone encountered something like this before?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sab669
  • 3,984
  • 8
  • 38
  • 75
  • 1
    My psychic powers tell me you are using `IsNumeric` and it returns `1` when you [don't expect it](http://stackoverflow.com/q/4430502/11683). – GSerg Jul 09 '15 at 12:27
  • 1
    It looks like the conversion is done in base 16, datatype int is max 4 bytes (or in base 16: 8 characters in the range 0..9+A..F), `AAAAAAAABA683` is 13 characters (+/- 7 bytes) – Mark Rotteveel Jul 09 '15 at 12:27
  • @GSerg sorry, I know I'm not providing a ton of info but I'm not a DB guy at all. No idea how to really look at what's going wrong under the hood on that side of things. But at a simple glance, complaining about type conversions *only when a field is too long* doesn't make sense. A non-number is a non-number and if that's actually the problem it should fail either way. – sab669 Jul 09 '15 at 12:34
  • @GSerg I do see an `IsNumeric` call on the field that has this bad data-- so you might be on to something. – sab669 Jul 09 '15 at 12:42

1 Answers1

1

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.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the input, while I wasn't necessarily expecting a clear-cut answer (don't think that's possible for this situation!) they're at least ideas to look into more. – sab669 Jul 09 '15 at 12:37