Mdtryhht

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

and

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.

You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:

SELECT TRY_CAST(FT.DOP AS SMALLINT) 
FROM TRACKING_DATA 
WHERE date > @mydate and identifier = 0000000000;

This will result in NULL being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL results, you should be good.

So it was the damn query plan it kept deciding to use.

The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.

The query in question was running a clustered seek on an index that covered the date but not the identifier this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.

Added a proper index for the where clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.