This question already has an answer here:
- At which point does SQL Server validate data for insertion 1 answer
- Implicit conversion causing error part of the time 1 answer
- UPDATE statement processing records that should be eliminated by WHERE clause 1 answer
- Intermittent “Conversion failed…” error 1 answer
- Why does query error with empty result set in SQL Server 2012? 2 answers
Something very odd is happening here.
I have a query that looks like this.
SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000
When run as a raw query it returns data just fine.
When i place it in a stored procedure that alters the where clause it throws this error.
Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2] The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.
So here is the oddity. I go through all the possible data for that where clause with a query like this.
SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000
SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000
And this is what i get back.
17 12 9 19 8 14 6 16 11 13 7 10 0 18 5 15 4
Nowhere does it have anything remotely too large for a
SMALLINT. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.
I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.